How to create a PivotTable in Transact/SQL?

Question

My source data table is

MemID Condition_ID Condtion_Result
----------------------------------
1     C1           0
1     C2           0
1     C3           0
1     C4           1
2     C1           0
2     C2           0
2     C3           0
2     C4           0

The expected view I want to create is ....

MemID C1 C2 C3 C4
------------------
1     1  0  0  1
2     0  0  0  1

Here is the other condition. In the above source table example , only 4 rows for a given MemID. This number will vary in the actual situation. My pivot table(or any other solution) should pick it any number of condition results and display them as columns. How to do it ?

Solution

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 @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

SET @cols = STUFF((SELECT distinct ',' + 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.