Link to home
Start Free TrialLog in
Avatar of ttist25
ttist25

asked on

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!
ASKER CERTIFIED SOLUTION
Avatar of Sharath S
Sharath S
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ttist25
ttist25

ASKER

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!
Avatar of ttist25

ASKER

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!
The "P" is used in the:
[...]
  FROM master.dbo.spt_values 
 WHERE type = 'P' 
[...]
--and if you run the statement below against your DB...is meaningless because...
select * from master.dbo.spt_values WHERE type = 'P'

Open in new window

The spt_values table is not mentioned in the the SQL Server documentation but it goes back to the Sybase days and there is some extremely minimal documentation in the Sybase online docs that can be summed up in this comment. Some people use the rows with type = 'P' to have a numbers table with the numbers from 1-2047.  This is, IMO, a bad practice. Undocumented system tables can be changed by Microsoft without warning, so you should avoid using them.
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a7cc9252-a1d1-4f51-9c35-7cd9720b507c/what-is-the-use-of-table-masterdbosptvalues?forum=transactsql
The table is also sometimes used in code snippets in MSDN blogs - but never in formal documentation - usually as a convenient source of a list of numbers. But as discussed elsewhere, creating your own source of numbers is a safer and more reliable solution than using an undocumented system table. There is even a Connect request to provide a 'proper', documented number table in SQL Server itself.