将行值转换为ColumnName

Question

表： 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

预期结果：

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

我试过用PIVOT，

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

结果：

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

但我没有得到预期的结果。帮我得到结果。

Answer 1

尝尝这个。使用window function获得结果。

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