Pivot Data from Rows to Multi Columns

I had a great challenge the other day at work for a client and thought I would post what I learned in hopes it would help others.  I need to pivot some data in SQL Server 2005 from multi rows into multi columns.

What did I all learn in this post?

• Pivot function
• Unpivot function
• row_number function
• Max function
• Case statement
• Left Join

Let's compare what kind of data I was given and how they wanted it outputted.

This is how the data was entered in the database (not the easiest data to work with):



But this is how the client wanted the data displayed:



 So I thought to myself how am I going to do this?  To make this easier for everyone the following are some temp tables I created to re-create what I was doing at work.

Here is the main table I used

DECLARE  @A TABLE (
   ID INT,
   value VARCHAR(10),
   instance INT,
   attributeID VARCHAR(4)
)

INSERT INTO @A VALUES (123,1,1,4391) 

INSERT INTO @A VALUES (123,190,1,4396)

INSERT INTO @A VALUES (123,2,2,4391)

INSERT INTO @A VALUES (123,190,2,4396)
INSERT INTO @A VALUES (124,3,1,4391) 

INSERT INTO @A VALUES (124,235,1,4396)

INSERT INTO @A VALUES (124,1,2,4391) 

INSERT INTO @A VALUES (124,200,2,4396)

Here is the department table:

DECLARE @Department TABLE(
   ID INT,
   Department VARCHAR(200)
)

INSERT INTO @Department VALUES (1,'IT')

INSERT INTO @Department VALUES (2,'Accounting')

INSERT INTO @Department VALUES (3,'Human Resources') 

INSERT INTO @Department VALUES (4,'Finance')

Here is the User table:

Declare @Users table
(

DECLARE @Users TABLE (
   UserId INT,
   FullName VARCHAR(200)
)

INSERT INTO @Users VALUES (190,'Robin Yount')

INSERT INTO @Users VALUES (235,'Ryan Braun') 

INSERT INTO @Users VALUES (200,'Prince Fielder')

Well if you could not tell I am a huge fan of Milwaukee Brewers fan!  Let's just hope they can beat the Cards in the playoffs this year.  Well back to the T-SQL stuff...

Before looking at the entire picture at once lets break down this down in parts.

First let's grab the basic information before pivoting this data.

SELECT ID, 
       value, 
       instance, 
       RIGHT('00' + CONVERT(VARCHAR,(row_number() OVER (partition BY ID ORDER BY ID,instance,attributeID))), 2) AS RowNum 

FROM @A WHERE attributeID IN (4391,4396) 

So what's the row number for you are asking?  Well we need that for the next step to be able to pivot the data.

Our second step is to pivot this data.

SELECT  ID,
   [01] AS Dept01,[02] AS Name01,
   [03] AS Dept02,[04] AS Name02FROM (
    SELECT ID, value, instance,
           RIGHT('00' + CONVERT(VARCHAR,(row_number() OVER (partition BY ID ORDER BY ID,instance,attributeID))), 2) AS RowNum
           FROM @A WHERE attributeID IN (4391,4396) 
    )A
pivot (MAX(value) FOR rownum IN ([01],[02],[03],[04])) AS pvt

Let's good so far right?  Well we don't want to show ID's multi times, but rather just once.  So at this point I was struggling to figure out the next step for a while.  I knew I had to unpivot the data, but how?  After doing some research I came across a very helpful blog.  So special thanks to Mangal for sharing the knowledge of SQL!

Our third step is to unpivot the data.  We want to create only three columns; ID, DeptID, UserID.  This will make it way easier when converting the rows to columns.


 

SELECT ID,
   DeptID, 
   UserID
   FROM ( 
       SELECT  ID,
           [01] AS Dept01,[02] AS Name01,
           [03] AS Dept02,[04] AS Name02
       FROM (
           SELECT ID, 
               value, 
               instance,
               RIGHT('00' + CONVERT(VARCHAR,(row_number() OVER (partition BY ID ORDER BY ID,instance,attributeID))), 2) AS RowNum
           FROM @A WHERE attributeID IN (4391,4396) 
       )A
       pivot (MAX(value) FOR rownum IN ([01],[02],[03],[04])) AS pvt          
   )main 
unpivot (UserID FOR Names IN (Name01,Name02))n
unpivot (DeptID FOR Dept IN (Dept01,Dept02))i 

WHERE RIGHT(Names,2)= RIGHT(Dept,2)

Now the hard part is done and what is left is the easy stuff!  So now I wanted to convert all those ID's such as User ID and Department number to their actually value.  Simple just had to join the correct table to the column.  Our fourth part is to join the department and user table. Please note we don't need the UserID and DeptID columns anymore I just added them so show you the relationship.

SELECT a.ID,
   a.UserID,
   b.FullName AS NamesValue,
   a.DeptID,
   c.Department
   FROM (
       SELECT ID,
           DeptID, 
           UserID
       FROM ( 
           SELECT  ID,
               [01] AS Dept01,[02] AS Name01,
               [03] AS Dept02,[04] AS Name02
           FROM (
               SELECT ID, 
                   value, 
                   instance,
                   RIGHT('00' + CONVERT(VARCHAR,(row_number() OVER (partition BY ID ORDER BY ID,instance,attributeID))), 2) AS RowNum
               FROM @A WHERE attributeID IN (4391,4396) 
           )A
           pivot (MAX(value) FOR rownum IN ([01],[02],[03],[04])) AS pvt          
       )main 
       unpivot(UserID FOR Names IN (Name01,Name02))n
       unpivot (DeptID FOR Dept IN (Dept01,Dept02))i
       WHERE RIGHT(Names,2)= RIGHT(Dept,2)
   )a

LEFT JOIN (SELECT * FROM @Users) AS b ON a.UserID = b.UserId 

LEFT JOIN (SELECT * FROM @Department) AS C ON A.DeptID = c.ID

So only one step left in this quest to display the data as the client wanted.  We just have to convert those rows into columns.  The last step is converting the rows into columns as seen below.

SELECT ID AS ReferralID
   , MAX(CASE WHEN DeptID = 1 THEN NamesValue END) [IT]
   , MAX(CASE WHEN DeptID = 2 THEN NamesValue END) [Accounting]
   , MAX(CASE WHEN DeptID = 3 THEN NamesValue END) [Human Resources]
   , MAX(CASE WHEN DeptID = 5 THEN NamesValue END) [Finance]

 FROM (  
   SELECT a.ID,
       a.UserID,
       b.FullName AS NamesValue,
       a.DeptID,
       c.Department
   FROM (
       SELECT ID,
           DeptID, 
           UserID
       FROM ( 
           SELECT  ID,
               [01] AS Dept01,[02] AS Name01,
               [03] AS Dept02,[04] AS Name02
           FROM (
               SELECT ID, 
                   value, 
                   instance,
                   RIGHT('00' + CONVERT(VARCHAR,(row_number() OVER (partition BY ID ORDER BY ID,instance,attributeID))), 2) AS RowNum
               FROM @A WHERE attributeID IN (4391,4396) 
           )A
           pivot (MAX(value) FOR rownum IN ([01],[02],[03],[04])) AS pvt          
       )main 
       unpivot (UserID FOR Names IN (Name01,Name02))n
       unpivot (DeptID FOR Dept IN (Dept01,Dept02))i
       WHERE RIGHT(Names,2)= RIGHT(Dept,2)
   )a
   LEFT JOIN (SELECT * FROM @Users) AS b ON a.UserID = b.UserId 
   LEFT JOIN (SELECT * FROM @Department) AS C ON A.DeptID = c.ID)d 

GROUP BY(ID)

So that's it.  Not to bad to write up.  Well hopefully this help you out as I learned some neat tricks with SQL Server.

TGIF!

Robert