troubleshooting Question

SQL Convert "vertical" data to "horizontal" data

Avatar of ttist25
ttist25 asked on
Microsoft SQL ServerGolang
5 Comments2 Solutions2316 ViewsLast Modified:
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!
ASKER CERTIFIED SOLUTION
Sharath S
Data Engineer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros