How to transpose/pivot SQL table

Question

I have a SQL table like below:

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 

As you can see the data for columns ID, Comments, StaffID and Dates are repeated (the same).

How do I make the above table into something like below:

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

Any help appreciated!

Answer 1

You could do something like this:

Test data

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')

Query

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

Edit

If you do not know how many step that you have. You need to do a dynamic pivot. Like this:

Test data

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')

Getting the unique steps:

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

The executing the dynamic pivot:

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)

And in my case dropping the table:

DROP TABLE tbl