T-SQL: Dynamic Pivot on Multiple Columns

T-SQL: Dynamic Pivot on Multiple Columns


How to make a dynamic PIVOT on multiple columns

The problem of transposing rows into columns is one of the most common problems discussed in MSDN Transact-SQL forum. Many times the problem of creating a dynamic pivot comes into the light. One thing that many people who ask this question forget is that such transposing is much easier to perform on the client side than on the server where we need to resort to dynamic query.

However, if we want to make such pivot dynamically, the important thing to understand is that writing dynamic query is only slightly more difficult than writing static query. In fact, when I am presented with the problem of dynamic pivot, I first figure out how static query should look like. Then making such query dynamically becomes rather trivial task.

I had written on the topic of dynamic pivot on multiple columns before in this blog post:  Dynamic PIVOT on multiple columns.

I don't want to re-tell what I already told in that blog so this article will show another example from the most recent thread on the topic of dynamic pivot.
In that thread I presented the following solution to the problem of dynamic pivot for unknown number of columns.

USE tempdb
GO
 
CREATE TABLE tblTest (
    Id INT
    ,Col_1 INT
    )
 
INSERT INTO tblTest
VALUES (
    1
    ,12345
    )
    ,(
    1
    ,23456
    )
    ,(
    1
    ,45678
    )
    ,(
    2
    ,57823
    )
    ,(
    2
    ,11111
    )
    ,(
    2
    ,34304
    )
    ,(
    2
    ,12344
    )
 
DECLARE @MaxCount INT;
 
SELECT @MaxCount = max(cnt)
FROM (
    SELECT Id
        ,count(Col_1) AS cnt
    FROM tblTest
    GROUP BY Id
    ) X;
 
DECLARE @SQL NVARCHAR(max)
    ,@i INT;
 
SET @i = 0;
SET @SQL = '';
 
WHILE @i < @MaxCount
BEGIN
    SET @i = @i + 1;
    SET @SQL = @Sql + ',
    MAX(CASE WHEN RowNo = ' + cast(@i AS NVARCHAR(10)) + ' THEN  Col_1 END) AS Col' + cast(@i AS NVARCHAR(10));
END
 
SET @SQL = N';WITH CTE AS (
   SELECT ID, Col_1, row_number() OVER (PARTITION BY ID ORDER BY Col_1) AS rowno
   FROM   tblTest
)
SELECT ID ' + @SQL + N'
FROM   CTE
GROUP  BY ID';
 
PRINT @SQL;
 
EXECUTE (@SQL);

In this solution the first step was figuring out the static solution using ROW_NUMBER() with partition approach. This is CASE based pivot although we could have used the true PIVOT syntax here instead. CASE based pivot is easier to use if we need to transpose multiple columns. Once we knew the static pivot, we were able to easily turn it into dynamic using WHILE loop.

Just for completion, I also show the same problem solved using PIVOT syntax:

DECLARE @MaxCount INT;
 
SELECT @MaxCount = max(cnt)
FROM (
    SELECT Id
        ,count(Col_1) AS cnt
    FROM tblTest
    GROUP BY Id
    ) X;
 
DECLARE @SQL NVARCHAR(max)
    ,@i INT;
 
SET @i = 0;
 
WHILE @i < @MaxCount
BEGIN
    SET @i = @i + 1;
    SET @SQL = COALESCE(@Sql + ', ', '') + 'Col' + cast(@i AS NVARCHAR(10));
END
 
SET @SQL = N';WITH CTE AS (
   SELECT ID, Col_1, ''Col'' + CAST(row_number() OVER (PARTITION BY ID ORDER BY Col_1) AS Varchar(10)) AS RowNo
   FROM   tblTest
)
SELECT *
FROM   CTE
PIVOT (MAX(Col_1) FOR RowNo IN (' + @SQL + N')) pvt';
 
PRINT @SQL;
 
EXECUTE (@SQL);

As you see, the code is very similar to the first solution, but using PIVOT syntax instead of CASE based pivot.

I hope to add more samples to this article as new opportunities present themselves.

There was another recent question about dynamic PIVOT where this article solution was right on target.

Additional Resources


See Also


Other Languages


This entry participated in the Technology Guru TechNet WiKi for May contest and won the Gold prize.
Sort by: Published Date | Most Recent | Most Useful
Comments
Page 1 of 2 (14 items) 12