Share via

T-SQL: Dynamic Pivot on Multiple Columns

  • Article

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.

The following blog post  Dynamic PIVOT on multiple columns also discusses the same problem.

In order to avoid re-telling what is already presented in that blog this article will show another example from the following thread on the topic of dynamic pivot.
In that thread the following solution was presented 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, let's see 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.

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 2013 contest and won the Gold prize.