sql server - Getting the top 6 items in a column to pivot to a row in SQL

Question

I'm having trouble getting a column to pivot in SQL. I'd like to pivot the top 6 results from one column into a row. The column I'm pivoting can have less than or more than 6 results to start with but I want to ignore anything beyond the top 6.

My Table1 looks like this:

ID | GroupID | CodeNum
----------------------
1  | 1       | 111
2  | 1       | 222
3  | 1       | 333
4  | 1       | 444
5  | 1       | 555
6  | 1       | 666
7  | 1       | 777
8  | 2       | 111
9  | 2       | 888
10 | 3       | 999

And I want my output to look like this:

GroupID | Code1 | Code2 | Code3 | Code4 | Code5 | Code6
-------------------------------------------------------
1       | 111   | 222   | 333   | 444   | 555   | 666
2       | 111   | 888   |       |       |       |
3       | 999   |       |       |       |       |

I've tried this code:

SELECT  GroupID
        , [Code1] = CASE WHEN rn = 1 THEN CodeNum END
        , [Code2] = CASE WHEN rn = 2 THEN CodeNum END
        , [Code3] = CASE WHEN rn = 3 THEN CodeNum END
        , [Code4] = CASE WHEN rn = 4 THEN CodeNum END
        , [Code5] = CASE WHEN rn = 5 THEN CodeNum END
        , [Code6] = CASE WHEN rn = 6 THEN CodeNum END
FROM    (
            SELECT  TOP 6 GroupID
                    , rn = ROW_NUMBER() OVER (ORDER BY ID)
                    , CodeNum
            FROM    Table1      
        ) q
GROUP BY
        GroupID

But I'm getting an error that says Column 'q.rn' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Is there a way to do this when CodeNum could have between 1 and 12 values per GroupID?

Answer 1

Since you are using a GROUP BY, you will need to group by all items in the SELECT or use an aggregate function.

You will want to use the following which will get the result and then pivot the rows into columns:

SELECT  GroupID
        , [Code1] = max(CASE WHEN rn = 1 THEN CodeNum END)
        , [Code2] = max(CASE WHEN rn = 2 THEN CodeNum END)
        , [Code3] = max(CASE WHEN rn = 3 THEN CodeNum END)
        , [Code4] = max(CASE WHEN rn = 4 THEN CodeNum END)
        , [Code5] = max(CASE WHEN rn = 5 THEN CodeNum END)
        , [Code6] = max(CASE WHEN rn = 6 THEN CodeNum END)
FROM    
(
  SELECT  GroupID
   , rn = ROW_NUMBER() OVER (PARTITION BY GroupID ORDER BY ID)
   , CodeNum
  FROM    Table1      
) q
GROUP BY GroupID

See SQL Fiddle with Demo

You will notice that I removed the TOP 6 and added a partition by GroupId. If you use the TOP, then you will only return the values for the first ID. The partition will return the rows for each ID, but the outer select is only returning the first 6 rows.

This gives the result:

| GROUPID | CODE1 |  CODE2 |  CODE3 |  CODE4 |  CODE5 |  CODE6 |
----------------------------------------------------------------
|       1 |   111 |    222 |    333 |    444 |    555 |    666 |
|       2 |   111 |    888 | (null) | (null) | (null) | (null) |
|       3 |   999 | (null) | (null) | (null) | (null) | (null) |

Note, depending on your version of SQL Server, this could also be written using the PIVOT function:

select GroupID,
  [1] as Code1, 
  [2] as Code2, 
  [3] as Code3, 
  [4] as Code4, 
  [5] as Code5, 
  [6] as Code6
from
(
  SELECT  GroupID
   , rn = ROW_NUMBER() OVER (PARTITION BY GroupID ORDER BY ID)
   , CodeNum
  FROM Table1    
) d
pivot
(
  max(codenum)
  for rn in ([1], [2], [3], [4], [5], [6])
) piv;

See SQL Fiddle with Demo