Return SQL results back as a single row for case scenarios

Question

So I have the following SQL query:

SELECT CASE
WHEN STATUS = 0 THEN 'Pending' 
WHEN STATUS = 1 THEN 'Pending' 
WHEN STATUS = 2 THEN 'Confirmed' 
WHEN STATUS = 3 THEN 'Pending' 
WHEN STATUS = 4 THEN 'Pending' 
WHEN STATUS = 5 THEN 'Pending' 
WHEN STATUS = 6 THEN 'Paid' 
WHEN STATUS = 7 THEN 'Closed' 
WHEN STATUS = 8 THEN 'Rejected' END AS Statuses
FROM ORDERS 
WHERE opportunityid = 4054
GROUP BY Statuses

Which returns the following results:

Pending
Confirmed

However, I am trying to achieve the following:

Pending,Confirmed

Can this be done without the use of a @declare function too.

http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string this is your answer — Murad, Mar 27 '17 at 11:25

score 2 · Accepted Answer · answered Mar 27 '17 at 11:30

I think you want something like this:

SELECT STUFF(MAX(CASE WHEN STATUS IN (0, 3, 4, 5) THEN ',Pending' ELSE '' END) +
             MAX(CASE WHEN STATUS IN (2) THEN ',Confirmed' ELSE '' END) +
             MAX(CASE WHEN STATUS IN (6) THEN ',Paid' ELSE '' END) +
             MAX(CASE WHEN STATUS IN (7) THEN ',Closed' ELSE '' END) +
             MAX(CASE WHEN STATUS IN (8) THEN ',Rejected' ELSE '' END),
             1, 1, ''
            ) as statuses
FROM ORDERS 
WHERE opportunityid = 4054;

score 0 · Answer 2 · answered Mar 27 '17 at 14:19

This will for status which are not predefined but exists in a table:

DECLARE @t table(STATUSES INT,opportunityid INT)
INSERT @t values(1,4054),(2,4054),(3,4054)
INSERT @t values(1,4055),(6,4055),(7,4055),(12,4055)

DECLARE @t2 table(sta int, txt varchar(30))
INSERT @t2 
values
        (0, 'Pending'),
        (1, 'Pending'),
        (2, 'Confirmed'), 
        (3, 'Pending'),
        (4, 'Pending'),
        (5, 'Pending'),
        (6, 'Paid'),
        (7, 'Closed'), 
        (8, 'Rejected')

;WITH CTE as
(
  SELECT distinct opportunityid FROM @t
)
SELECT 
    t.*, 
    STUFF(( 
        SELECT distinct ',' + coalesce([txt], 'Unknown')
        FROM @t t1 
        LEFT JOIN
        @t2 x
        ON x.sta = t1.STATUSES
        WHERE t1.opportunityid = t.opportunityid
        for xml path(''), type 
    ).value('.', 'varchar(max)'), 1, 1, '') [status] 
FROM cte t

Return SQL results back as a single row for case scenarios

2 Answers2