We help IT Professionals succeed at work.

SQL Convert "vertical" data to "horizontal" data

ttist25
ttist25 asked
on
2,289 Views
Last Modified: 2017-04-20
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

Data Engineer
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

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!