How To Create A Pivottable In Transact/sql?

My source data table is MemID Condition_ID Condtion_Result ---------------------------------- 1 C1 0 1 C2 0 1 C3 0 1 C4 1 2

Solution 1:

You need to use a PIVOT. You can use either a STATIC PIVOT where you know the values of the columns to transform or a DYNAMIC PIVOT where the columns are unknown until execution time.

Static Pivot (See SQL Fiddle with Demo):

select *
from 
(
    select memid, Condition_id, Condition_Result
    from t
) x
pivot
(
    sum(condition_result)
    for condition_id in ([C1], [C2], [C3], [C4])
) p

Dynamic Pivot (See SQL Fiddle with Demo):

DECLARE@colsAS NVARCHAR(MAX),
    @queryAS NVARCHAR(MAX)

SET@cols= STUFF((SELECTdistinct','+ QUOTENAME(c.condition_id) 
            FROM t c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set@query='SELECT memid, '+@cols+' from 
            (
                select MemId, Condition_id, condition_result
                from t
           ) x
            pivot 
            (
                sum(condition_result)
                for condition_id in ('+@cols+')
            ) p 'execute(@query)

Both will generate the same results.