Pivot SQL Statement
I have a table like this (of course there are many more values but you get the idea):
ID Name
--- ----
1 A
1 B
2 C
3 D
4 A
4 D
4 E
4 F
4 G
4 H
I want to write a query that would output this, given that an ID cannot have more than 6 names.
开发者_StackOverflow社区ID Name1 Name2 Name3 Name4 Name5 Name6
--- ------ ------ ------ ------ ------ -----
1 A B
2 C
3 D
4 A D E F G H
Try:
WITH rows AS (
SELECT t.id,
t.name,
ROW_NUMBER() OVER (PARTITION BY t.id ORDER BY t.name) 'rank'
FROM TABLE t)
SELECT r.id,
MAX(CASE WHEN r.rank = 1 THEN r.name ELSE NULL END) AS Name1,
MAX(CASE WHEN r.rank = 2 THEN r.name ELSE NULL END) AS Name2,
MAX(CASE WHEN r.rank = 3 THEN r.name ELSE NULL END) AS Name3,
MAX(CASE WHEN r.rank = 4 THEN r.name ELSE NULL END) AS Name4,
MAX(CASE WHEN r.rank = 5 THEN r.name ELSE NULL END) AS Name5,
MAX(CASE WHEN r.rank = 6 THEN r.name ELSE NULL END) AS Name6,
FROM rows r
GROUP BY r.id
Non CTE equivalent:
SELECT r.id,
MAX(CASE WHEN r.rank = 1 THEN r.name ELSE NULL END) AS Name1,
MAX(CASE WHEN r.rank = 2 THEN r.name ELSE NULL END) AS Name2,
MAX(CASE WHEN r.rank = 3 THEN r.name ELSE NULL END) AS Name3,
MAX(CASE WHEN r.rank = 4 THEN r.name ELSE NULL END) AS Name4,
MAX(CASE WHEN r.rank = 5 THEN r.name ELSE NULL END) AS Name5,
MAX(CASE WHEN r.rank = 6 THEN r.name ELSE NULL END) AS Name6,
FROM (SELECT t.id,
t.name,
ROW_NUMBER() OVER (PARTITION BY t.id ORDER BY t.name) 'rank'
FROM TABLE t) r
GROUP BY r.id
Reference:
- ROW_NUMBER()
I created a stored procedure named pivot_query to make the PIVOT statement a little more flexible. The source for it is here. There is also an example of how to use it.
Borrowing a piece of code from OMG Ponies below, and changing the query a bit, then the call to pivot_query would look like this:
declare @mySQL varchar(MAX)
set @mySQL = '
SELECT
t.id,
t.name,
''Name'' + cast(ROW_NUMBER() OVER (PARTITION BY t.id ORDER BY t.name) as varchar(2)) rank
FROM
TestData t'
exec pivot_query @mySQL, 'Id', 'rank', 'max(Name)'
and the results now look like this:
Id Name1 Name2 Name3 Name4 Name5 Name6
---------- ----- ----- ----- ----- ----- -----
1 A B NULL NULL NULL NULL
2 C NULL NULL NULL NULL NULL
3 D NULL NULL NULL NULL NULL
4 A D E F G H
Not exactly sure what you're trying to show, though. :-)
This will not intrinsically limit the output to the 6 name columns though, it will keep going up unless you add a where clause to specifically exclude ranks above 6.
精彩评论