Pivot SQL Statement

Question

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.

ID      Name1        Name2       Name3     Name4     Name5    Name6
---    ------        ------     ------    ------    ------    -----
1        A             B
2        C
3        D
4        A             D           E         F         G        H

Answer 1

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.

Ron

Answer 2

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()