将行转置为列 - 在 SQL Server 2008 中使用 id 列进行透视

Question

我有一个包含列logid、skilllevel、logskill列的表，数据示例如下所示：

logid  skilllevel  logonskill  skillposition
--------------------------------------------
101    90          1           1
101    40          2           2
102    30          4           1

我想让它像下面这样安排：

logid  skilllevel1  skilllevel2 skilllevel3 logonskill1 logonskill2 logonskill3 
101    90           40          60          1           2           3
102    30           20          10          4           5           6

skilllevel1对应于logonskill1，依此类推skillposition是logonskill的子字符串

我怎样才能做到这一点？

Answer 1

您可以使用此查询。

SELECT * FROM (
    select logid, skilllevel val, 'skilllevel' + CONVERT(VARCHAR,skillposition) colName  from MyTable
    UNION ALL 
    select logid, logonskill val, 'logonskill' + CONVERT(VARCHAR,skillposition) colName  from MyTable
) SRC
PIVOT (MAX(val) FOR colName IN ([skilllevel1],[skilllevel2],[skilllevel3],[logonskill1],[logonskill2],[logonskill3])) AS PVT