CODEWITHSUNDEEP
sqlsql-serverdatabase
Saravana Kumar
Saravana Kumar

Reputation: 3729

Converting row values into ColumnName

Table: SampleTbl

 Values    Name
---------------------
23          id
33          b_id
2014-12-10  Date
55          t_id
NULL        p_id
NULL        comments
24            id
34           b_id
2014-12-11   Date
56           t_id
NULL         p_id
NULL        comments

Expected Result:

id     b_id     Date       t_id    p_id  comments
 ------------------------------------------------------
 23       33    2014-12-10    55    NULL  NULL
 24       34    2014-12-10    56    NULL  NULL

I have tried using PIVOT, 

SELECT * FROM 
SampleTbl
Pivot (MIN ([Values]) FOR Name IN ([id],[b_id],[Date],[t_id],[p_id],[comments])) piv

Result I Get:

id     b_id     Date       t_id    p_id   comments
---------------------------------------------------
23      33     2014-12-10   55      NULL    NULL

but i din't get the expected result. Help me to get the result.

Upvotes: 1

Views: 78

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93694

Try this. Use window function to get the result.

SELECT [id],[b_id],[Date],
       [t_id],[p_id],[comments]
FROM   (SELECT Row_number()OVER(partition BY name ORDER BY [values]) rn,*
        FROM   SampleTbl) A
       PIVOT (Min ([Values])
             FOR Name IN ([id],[b_id],[Date],
                          [t_id],[p_id],[comments])) piv

SQL FIDDLE DEMO

Upvotes: 2

Related Questions