CODEWITHSUNDEEP
sqlsql-serverpivot
dthree
dthree

Reputation: 20740

Table Pivot in SQL Server

I have a table of phone numbers connected to persons, and the numbers are already partitioned by the person ID:

person_id   number      row_count
1           5556667777  1
1           5435346523  2
1           7645634344  3
2           5556667777  1
2           5435346523  2
3           7645634344  1

The amount of numbers per person can reach up to twenty, but I already have them sequenced by priority, and want to make five columns of the first five numbers per person, leave null the empty colums and ignore numbers past the fifth column:

person_id   num_1       num_2       num_3       num_4       num_5
1           5556667777  5435346523  7645634344  null        null
2           5556667777  5435346523  null        null        null
3           7645634344  null        null        null        null

I have been looking around and it looks like pivot is the right command, but I can't wrap my head around a similar enough example to fully understand it.

Upvotes: 1

Views: 50

Answers (1)

Hart CO
Hart CO

Reputation: 34774

PIVOT takes some time to get comfortable with, but luckily your case is a pretty straightforward example:

SELECT person_id, [1] Num_1,[2] Num_2,[3] Num_3,[4] Num_4,[5] Num_5
FROM Table1
PIVOT(MAX(number) FOR row_count IN ([1],[2],[3],[4],[5]))pvt

Demo: SQL Fiddle

Since you're only using 5 numbers and ignoring the rest, it's easy enough to hard-code in the 5 values used, but this can also be done dynamically.

Upvotes: 2

Related Questions