Match and coalesce multiple abbreviated possibilities

I have a table of project ID data and another of abbreviated circumstances related to the project. I would like to have the query output all the abbreviated options in a coalesced and descriptive format.

ProjectID is int
SpecialConditions is varchar

Query:

| ProjectID | SpecialConditions |
| --------  | ----------------- |
| 2023-0001 |        00         |
| 2023-0002 |        CBSP       |
| 2023-0003 |        KCB        |
| 2023-0004 |                   |
| 2023-0005 |        K          |
| 2023-0006 |        WMCBSP     |

List of abbreviated details:

00 = No Fruits
CB = Cranberry
K  = Kiwi
SP = Sugar Plum
WM = Watermelon

Desired output:

| ProjectID | SpecialConditions                 |
| --------  | --------------------------------- |
| 2023-0001 | No Fruits                         |
| 2023-0002 | Cranberry, Sugar Plum             |
| 2023-0003 | Kiwi, Cranberry                   |
| 2023-0004 |                                   |
| 2023-0005 | Kiwi                              |
| 2023-0006 | Watermelon, Cranberry, Sugar Plum |

Previously I was using CASE, then I started realizing multiple conditions exist in different order which requires me to rewrite the query.

SELECT CASE When SpecialConditions = '00' then 'No Fruits'
When SpecialConditions = 'CB' then 'Cranberry'
When SpecialConditions = 'K' then 'Kiwi'
When SpecialConditions = 'SP' then 'Sugar Plum'
When SpecialConditions = 'WM' then 'Watermelon'
When SpecialConditions = 'SPWM' then 'Sugar Plum, Watermelon'
When SpecialConditions = 'WMSP' then 'Sugar Plum, Watermelon'
else (coalesce(SpecialConditions, '') END as 'Fruits'`
From Projects

Looking for any feedback on how I can write this query

If you define and populate a lookup table that maps codes to descriptions, you can match lookup table entries using a LIKE operator or CHARINDEX() function. The STRING_AGG() function can then be used to combine the results.

SELECT P.ProjectID, P.SpecialConditions, A.Fruits
FROM Projects P
CROSS APPLY (
    SELECT COALESCE(
        STRING_AGG(FL.Description, ', ') WITHIN GROUP(ORDER BY M.MatchPos)
        , P.SpecialConditions
        , '') AS Fruits
    FROM FruitLookup FL
    CROSS APPLY (SELECT CHARINDEX(FL.Code, P.SpecialConditions) AS MatchPos) M
    WHERE M.MatchPos > 0
) A

For SQL Server 2016 and earlier, the old FOR XML hack can be used in place of the STRING_AGG() function.

SELECT P.ProjectID, P.SpecialConditions, A.Fruits
FROM Projects P
CROSS APPLY (
    SELECT COALESCE(
        STUFF((
            SELECT ', ' + FL.Description
            FROM FruitLookup FL
            CROSS APPLY (SELECT CHARINDEX(FL.Code, P.SpecialConditions) AS MatchPos) M
            WHERE M.MatchPos > 0
            ORDER BY M.MatchPos
            FOR XML PATH(''), TYPE
        ).value('.', 'varchar(max)'), 1, 2, '')
        , P.SpecialConditions
        , '') AS Fruits
) A

The above will order the mapped names in the same order as they appear in the original string (MatchPos in the above code).

Note that the matching logic does not protect against accidental matches caused by adjacent codes that contain character sequences that happen to match other codes. You will need to figure out a strategy to handle those cases.

Sample results:

See this db<>fiddle for a working example that includes both versions of the above code.