How To Transpose Rows Of Data Into A Single Row With Different Columns In Sql Server

Currently I have data from #temp where I extract it from other tables. The #temp table format is as follows WorkerID MainDoc SubDoc Value TimeStamp -------- -------- -----

Solution 1:

My apologizes since the original answer has some logic errors, finally I could get some time to test it

CREATETABLE #MyTable (workerid int, maindoc varchar(5), subdoc varchar(2), valuevarchar(3), timestampvarchar(5))

insert #MyTable values 
(1234, 'Doc1', 'A1', 'abc', '11:40'),
(1234, 'Doc1', 'A2', 'def', '11:40'),
(1234, 'Doc1', 'B1', '30' , '11.40'),
(1234, 'Doc1', 'B2', '40' , '11:40'),
(1234, 'Doc1', 'C1', '50' , '11:40'),
(1234, 'Doc1', 'C1', '51' , '11:50'),
(1234, 'Doc1', 'C2', '60' , '11:40'),
(1234, 'Doc1', 'C2', '61' , '11:50'),
(1235, 'Doc1', 'A1', 'fgf', '11:55'),
(1235, 'Doc1', 'A2', 'vbv', '11:55');

In this approach, my first step is to use a CTE to get the ValueNumber and take the last value for each SubDoc, then I Pivot the values....

WITH subDocs AS (
    SELECTDISTINCT ROW_NUMBER() OVER (ORDERBY SubDoc) vn, SubDoc FROM (SELECTDISTINCT SubDoc FROM #MyTable) A
), cte AS (
    SELECT 
        WorkerID, 
        MainDoc, 
        T.SubDoc,
        Value,
        ROW_NUMBER() OVER (PARTITION BY WorkerID, MainDoc, T.SubDoc ORDERBY TimeStamp DESC) AS rn, 
        vn
    FROM #MyTable T
    JOIN subDocs S ON T.subdoc = S.subdoc 
)
SELECT WorkerID, MainDoc, [1] as [Value(1)], [2] as [Value(2)], [3] as [Value(3)], [4] as [Value(4)], [5] as [Value(5)], [6] as [Value(6)]
FROM (
    SELECT WorkerID, Maindoc, vn, Value FROM cte WHERE rn = 1
) Source
PIVOT (
    MAX(Value) FOR vn IN ([1],[2],[3],[4],[5],[6])
) PVT

But most probably the number of columns that you want is not fixed, and you need to use dynamic SQL to get it

DECLARE@SQL NVARCHAR(MAX);
DECLARE@SubDocCountINTDECLARE@ColumnNames NVARCHAR(MAX)
DECLARE@PivotValues NVARCHAR(MAX)

SELECT TOP 1@SubDocCount=COUNT(DISTINCT Subdoc) FROM #MyTable

;WITH cols AS (
    SELECT1as num
    UNIONALLSELECT num +1FROM cols WHERE num <@SubDocCount 
)
SELECT@ColumnNames= STUFF(
             (SELECT','+ QUOTENAME(CAST(num ASVARCHAR)) +' AS '+ QUOTENAME('Value'+ QUOTENAME(CAST(num ASVARCHAR),'('))
              FROM cols
              FOR XML PATH (''))
             , 1, 1, ''),
    @PivotValues= STUFF(
             (SELECT','+ QUOTENAME(CAST(num ASVARCHAR)) 
              FROM cols
              FOR XML PATH (''))
             , 1, 1, '')

SET@SQL= N'
    WITH subDocs AS (
        SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY SubDoc) vn, SubDoc FROM (SELECT DISTINCT SubDoc FROM #MyTable) A
    ), cte AS (
        SELECT 
            WorkerID, 
            MainDoc, 
            T.SubDoc,
            Value,
            ROW_NUMBER() OVER (PARTITION BY WorkerID, MainDoc, T.SubDoc ORDER BY TimeStamp DESC) AS rn, 
            vn
        FROM #MyTable T
        JOIN subDocs S ON T.subdoc = S.subdoc 
    )
    SELECT WorkerID, MainDoc, '+@ColumnNames+'
    FROM (
        SELECT WorkerID, Maindoc, vn, Value FROM cte WHERE rn = 1
    ) Source
    PIVOT (
        MAX(Value) FOR vn IN ('+@PivotValues+')
    ) PVT'EXEC (@SQL)

Note there are 3 different WorkerID's in your sample input, given the expected result I assumed 1224 and 1234 as a typo