SQL Convert "vertical" data to "horizontal" data

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!
LVL 1
ttist25Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SharathData EngineerCommented:
/*
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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

0
ttist25Author 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!
0
ttist25Author 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!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.