By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Projects (
ProjectID VARCHAR(100),
SpecialConditions VARCHAR(100)
)
INSERT Projects
VALUES
('2023-0001', '00'),
('2023-0002', 'CBSP'),
('2023-0003', 'KCB'),
('2023-0004', ''),
('2023-0005', 'K'),
('2023-0006', 'WMCBSP'),
('2023-0007', 'SPCBWM') -- reversed
CREATE TABLE FruitLookup (
Code VARCHAR(10),
Description VARCHAR(100)
)
INSERT FruitLookup
VALUES
('00', 'No Fruits'),
('CB', 'Cranberry'),
('K', 'Kiwi'),
('SP', 'Sugar Plum'),
('WM', 'Watermelon')
12 rows affected
-- SQL Server 2017 and later
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
| ProjectID | SpecialConditions | Fruits |
|---|---|---|
| 2023-0001 | 00 | No Fruits |
| 2023-0002 | CBSP | Cranberry, Sugar Plum |
| 2023-0003 | KCB | Kiwi, Cranberry |
| 2023-0004 | ||
| 2023-0005 | K | Kiwi |
| 2023-0006 | WMCBSP | Watermelon, Cranberry, Sugar Plum |
| 2023-0007 | SPCBWM | Sugar Plum, Cranberry, Watermelon |
-- SQL Server 2016 and earlier
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
| ProjectID | SpecialConditions | Fruits |
|---|---|---|
| 2023-0001 | 00 | No Fruits |
| 2023-0002 | CBSP | Cranberry, Sugar Plum |
| 2023-0003 | KCB | Kiwi, Cranberry |
| 2023-0004 | ||
| 2023-0005 | K | Kiwi |
| 2023-0006 | WMCBSP | Watermelon, Cranberry, Sugar Plum |
| 2023-0007 | SPCBWM | Sugar Plum, Cranberry, Watermelon |