Pivot and Format Result Set with custom string

Question

I am using SQL Server 2014. Would like to know the better way to get the result for the following table. Source Table

UserId --- Program 
100 --------        P1
100 -------- P2
101 -------- P3
103 --------  P1

Desired Output

UserId ------ P1 --- P2 ----P3
100  -------- YES----YES----NO
101  -------- NO ---- NO ---YES
103 -------- YES ---- NO --YES

Please Note

UserId, Program are dynamically populated.

If you won't know the programs until execution time, you would need a dynamic pivot. — Bacon Bits, Nov 28 '17 at 22:18
Possible duplicate of [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Bacon Bits, Nov 28 '17 at 22:18
@BaconBits - In your reference link, the pivoted rows of the columns aren't computed (result based). — Muruga, Nov 28 '17 at 22:24
You'll have to convert NULLs to NO and non-NULLs to YES, if that's what you mean. Otherwise, I have no idea what you're asking. — Bacon Bits, Nov 28 '17 at 22:27

Kashif Qureshi · Accepted Answer · 2017-11-28T22:36:15.363

one way to do it is like this:

SELECT userid, 
       CASE 
         WHEN Sum(CASE 
                    WHEN program = 'p1' THEN 1 
                    ELSE 0 
                  END) = 1 THEN 'YES' 
         ELSE 'No' 
       END AS p1, 
       CASE 
         WHEN Sum(CASE 
                    WHEN program = 'p2' THEN 1 
                    ELSE 0 
                  END) = 1 THEN 'YES' 
         ELSE 'No' 
       END AS p2, 
       CASE 
         WHEN Sum(CASE 
                    WHEN program = 'p3' THEN 1 
                    ELSE 0 
                  END) = 1 THEN 'YES' 
         ELSE 'No' 
       END AS p3 
FROM   table
GROUP  BY userid

Dynamically you can do something like this:

create table #temp 
(
    userid int,
    program varchar(20)

)

insert into #temp
values(100, 'P1'),
(100 ,'P2'),
(101 , 'P3'),
(103 ,  'P1')

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
    @computedCols  AS NVARCHAR(MAX)


SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.program) 
            FROM #temp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
SET @computedCols = STUFF((SELECT distinct ',' + ' case when ' + QUOTENAME(c.program) + ' = 1 Then ''Yes'' ELSE ''NO'' end as '+  QUOTENAME(c.program)
            FROM #temp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT userid, ' + @computedCols + ' from 
            (
                select *, 1 as val
                from #temp
           ) x
            pivot 
            (
                 max(val)
                for program in (' + @cols + ')
            ) p '


execute(@query)

Above is extension of accepted answer from this post SQL Server dynamic PIVOT query?

Programs are dynamically generated. – Muruga Nov 28 '17 at 22:27 — Muruga, Nov 28 '17 at 22:27

Pivot and Format Result Set with custom string

Please Note

1 Answers1