sql server-如何转换/透视sql表

Question

我有一个SQL表，如下所示：

ID    StepID   Rating   Comments     StaffID    Date
-------------------------------------------------------------
1     1        6          blah blah     1025      2014-03-20 
1     2        6          blah blah     1025      2014-03-20 
1     3        8          blah blah     1025      2014-03-20 
1     4        7          blah blah     1025      2014-03-20 

正如您所看到的，ID、Comments、StaffID和Dates列的数据是重复的（相同）。

我如何将上面的表格变成下面的样子：

ID    Step1   Step2  Step3  Step4  Comments     StaffID    Date
----------------------------------------------------------------------------------
1      6       6      8      7    blah blah     1025    2014-03-20 

感谢您的帮助！

Answer 1

您可以这样做：

测试数据

DECLARE @tbl TABLE(ID INT,StepID int,Rating INT, 
                   Comments VARCHAR(200), StaffID INT,Date DATETIME)
INSERT INTO @tbl
VALUES
(1,1,6,'blah blah',1025,'2014-03-20'),
(1,2,6,'blah blah',1025,'2014-03-20'),
(1,3,8,'blah blah',1025,'2014-03-20'),
(1,4,7,'blah blah',1025,'2014-03-20')

查询

SELECT
    pvt.ID,
    pvt.[1] AS Step1,
    pvt.[2] AS Step2,
    pvt.[3] AS Step3,
    pvt.[4] AS Step4,
    pvt.Comments,
    pvt.StaffID,
    pvt.Date
FROM
(
    SELECT 
        * 
    FROM 
        @tbl AS tbl
) AS sourceTable
PIVOT
(
    MAX(Rating)
    FOR StepID IN ([1],[2],[3],[4])
) AS pvt

编辑

如果你不知道你有多少步。你需要做一个动态枢轴。像这样：

测试数据

CREATE TABLE tbl
(
    ID INT,
    StepID int,
    Rating INT, 
    Comments VARCHAR(200), 
    StaffID INT,
    Date DATETIME
)
INSERT INTO tbl
VALUES
(1,1,6,'blah blah',1025,'2014-03-20'),
(1,2,6,'blah blah',1025,'2014-03-20'),
(1,3,8,'blah blah',1025,'2014-03-20'),
(1,4,7,'blah blah',1025,'2014-03-20')

获得独特的步骤：

DECLARE @cols VARCHAR(MAX)
SELECT @cols=STUFF
(
    (
        SELECT DISTINCT
            ',' +QUOTENAME('Step'+CAST(tbl.StepID AS VARCHAR(100)))
        FROM
            tbl
        FOR XML PATH('')
    )
,1,1,'')

执行动态枢轴：

DECLARE @query NVARCHAR(4000)=
'SELECT
    *
FROM
(
    SELECT 
        tbl.Comments,
        tbl.Date,
        tbl.ID,
        tbl.Rating,
        tbl.StaffID,
        ''Step''+CAST(tbl.StepID AS VARCHAR(100)) AS StepID
    FROM 
        tbl
) AS sourceTable
PIVOT
(
    MAX(Rating)
    FOR StepID IN ('+@cols+')
) AS pvt
 DROP TABLE tbl'
 EXECUTE(@query)

在我的案例中，放弃了桌子：

DROP TABLE tbl