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     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 ?

Answers


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.


Need Your Help

Getting maximum value of float in SQL programmatically

sql sql-server tsql floating-point

Is there an method for programmatically (in T-SQL) retrieving the maximum (and minimum) value of a datatype? That it would act like float.MaxValue in C#.

iOS check if application has access to microphone

ios objective-c swift microphone avaudiosession

With the introduction of iOS 7, applications have to request microphone access when they want to record audio.