SQL Convert "vertical" data to "horizontal" data

ttist25
ttist25 used Ask the Experts™
on
Hey there!

Something I come across frequently is having to take relational data and place it in a "rectangular" format for analysts.  

Take the following tables for example:

PersonTable:
-----------------------------------
PersonID | Name | Age
-----------------------------------
1               | Bob     | 18
-----------------------------------
2               | Alice    | 45
----------------------------------
3               | Fred    | 104
----------------------------------
4               | Jane    | 22
----------------------------------

TrackingNumberTable:
-----------------------------------------
PersonID | TrackingNumber
-----------------------------------------
1                | A1211
2                | A1311
1                | B1321
3                | A3241
1                | C1211
-----------------------------------------

The desired Output table would look like this:
-------------------------------------------------------------------------------------------
PersonID | Name | Age | Tracking_1 | Tracking_2 | Tracking_3
-------------------------------------------------------------------------------------------
1               | Bob     | 18   | A1211         | B1321         | C1211
-------------------------------------------------------------------------------------------
2               | Alice    | 45   | A1311         | NULL          | NULL
-------------------------------------------------------------------------------------------
3               | Fred    | 104 | A3241         | NULL          | NULL
-------------------------------------------------------------------------------------------
4               | Jane     | 22   | NULL          | NULL          | NULL
-------------------------------------------------------------------------------------------

I've been looking at PIVOT as a potential solution for this.  My ultimate goal is to get to the point where I can dynamically create the number of required columns based on the max count of TrackingNumbers for a PersonID but for now - baby steps - I just want to get the values into hard coded columns.

So far I've got this:
SELECT * FROM
(SELECT p.PersonID, p.Name, p.Age, t.TrackingNumber
FROM PersonTable AS p
INNER JOIN TrackingNumberTable AS t
ON p.PersonID=t.PersonID) AS BaseData
PIVOT
( -- not sure what the heck to put in here )
AS MyDesiredOutputTable

What do you think?  Given the ultimate goal of dynamically created columns, is PIVOT the right path to take?  If so - what would go in my PIVOT() part?

Thanks in advance for any help!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Data Engineer
Commented:
/*
create table PersonTable (PersonID int, Name varchar(100), Age int)
insert into PersonTable values (1,'Bob',18),(2,'Alice',45),(3,'Fred',104),(4,'Jane',22)

create table TrackingNumberTable(PersonID int, TrackingNumber varchar(100))
insert into TrackingNumberTable values (1,'A1211'),(2,'A1311'),(1,'B1321'),(3,'A3241'),(1,'C1211')
*/

DECLARE @sql NVARCHAR(MAX)
SELECT @sql = '
;WITH CTE AS (
SELECT *,ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY TrackingNumber) rn
  FROM TrackingNumberTable),
  CTE2 AS (
SELECT PersonID,
'
;WITH CTE AS (
SELECT 'MAX(CASE rn WHEN ' + CONVERT(VARCHAR,number) + ' THEN TrackingNumber END) TrackingNumber_' + CONVERT(VARCHAR,number) Col
  FROM master.dbo.spt_values 
 WHERE type = 'P' 
   AND number BETWEEN 1 AND (SELECT MAX(Cnt) FROM (SELECT COUNT(*) Cnt FROM TrackingNumberTable GROUP BY PersonID) t1))
 select @sql += rtrim(substring(isnull((select ','+Col from CTE for xml path('')),' '),2,2000))
SELECT @sql += ' 
FROM CTE
 GROUP BY PersonID)
 SELECT p.PersonID, p.Name, p.Age, c.*
   FROM CTE2 c
  JOIN PersonTable p
    ON c.PersonID = p.PersonID'
EXEC(@sql)
/*
PersonID	Name	Age	PersonID	TrackingNumber_1	TrackingNumber_2	TrackingNumber_3
1	Bob	18	1	A1211	B1321	C1211
2	Alice	45	2	A1311	NULL	NULL
3	Fred	104	3	A3241	NULL	NULL
*/

Open in new window

Ryan ChongSoftware Team Lead
Commented:
or something like:

declare @PersonTable table
(
	PersonID int,
	Name varchar(60),
	Age int
);
insert into @PersonTable
values
(1, 'Bob', 18),
(2, 'Alice', 45),
(3, 'Fred', 104),
(4, 'Jane', 22);

declare @TrackingNumberTable table
(
	PersonID int,
	TrackingNumber varchar(20)
);
insert into @TrackingNumberTable
values
(1, 'A1211'),
(2, 'A1311'),
(1, 'B1321'),
(3, 'A3241'),
(1, 'C1211');

with cte as
(
	SELECT p.PersonID, p.Name, p.Age, t.TrackingNumber,
	row_number() over (partition by p.PersonID Order By (Select 1)) idx
	FROM @PersonTable AS p
	left JOIN @TrackingNumberTable AS t
	ON p.PersonID=t.PersonID
)
Select a.PersonID, a.Name, a.Age,
t1.TrackingNumber Tracking1,
t2.TrackingNumber Tracking2,
t3.TrackingNumber Tracking3
from cte a
left join
(
	select PersonID, TrackingNumber from cte where idx = 1
) t1 on a.PersonID = t1.PersonID
left join
(
	select PersonID, TrackingNumber from cte where idx = 2
) t2 on a.PersonID = t2.PersonID
left join
(
	select PersonID, TrackingNumber from cte where idx = 3
) t3 on a.PersonID = t3.PersonID
group by a.PersonID, a.Name, a.Age,
t1.TrackingNumber,
t2.TrackingNumber,
t3.TrackingNumber

Open in new window

Author

Commented:
Awesome!  Both solutions work well.  Sharath took the extra step to include dynamic columns.  I had actually come to my own (non-dynamic) solution yesterday afternoon using ROW_NUMBER after watching this YouTube video https://www.youtube.com/watch?v=C0mQqDnF7wQ 

Thanks so much for the responses!

Author

Commented:
Hi Sharath,

I'm trying to understand what's happening in your query - on line 20:
WHERE type = 'P'

What is that piece doing?  What is 'P'?

Thanks again!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial