Resources For IT Professionals
Post an article
Wikis - Page Details
  • First published by (eMicrosoft Partne)
  • When: 27 May 2013 5:16 PM
  • Last revision by (eMicrosoft Partne)
  • When: 12 Sep 2013 11:07 AM
  • Revisions: 24
  • Comments: 16
Options
RSSSubscribe to Article (RSS)
Can You Improve This Article?
Positively! Click Sign In to add the tip, solution, correction or comment that will help other users.

Report inappropriate content using these instructions.
Wiki  >  TechNet Articles  >  T-SQL: Dynamic Pivot on Multiple Columns

T-SQL: Dynamic Pivot on Multiple Columns

T-SQL: Dynamic Pivot on Multiple Columns

Table of Contents

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
 
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.

This entry participated in the Technology Guru TechNet WiKi for May contest and won the Gold prize.

See also


Other Languages

, , , , , , , , , , , [Edit tags]
Leave a Comment
  • Please add 4 and 6 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • 28 May 2013 6:01 AM

    Naomi  N edited Revision 3. Comment: Explanation of solution

  • 28 May 2013 6:11 AM

    Naomi  N edited Revision 4. Comment: Added true PIVOT syntax

  • 2 Jun 2013 4:08 PM

    Naomi  N edited Revision 6. Comment: Adjusted participation link

  • 18 Jun 2013 5:53 PM

    Ed Price - MSFT edited Revision 7. Comment: Making the technology more obvious in the title.

  • 26 Jul 2013 2:47 PM

    Naomi  N edited Revision 19. Comment: Added one more link to a recent thread

  • 27 May 2013 6:13 PM

    Naomi  N edited Original. Comment: More content

Page 1 of 1 (6 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
Page 1 of 2 (16 items) 12