wendyreindeer
New Member
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:\[code\]ID | GroupID | CodeNum----------------------1 | 1 | 1112 | 1 | 2223 | 1 | 3334 | 1 | 4445 | 1 | 5556 | 1 | 6667 | 1 | 7778 | 2 | 1119 | 2 | 88810 | 3 | 999\[/code\]And I want my output to look like this:\[code\]GroupID | Code1 | Code2 | Code3 | Code4 | Code5 | Code6-------------------------------------------------------1 | 111 | 222 | 333 | 444 | 555 | 6662 | 111 | 888 | | | |3 | 999 | | | | |\[/code\]I've tried this code:\[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 ENDFROM ( SELECT TOP 6 GroupID , rn = ROW_NUMBER() OVER (ORDER BY ID) , CodeNum FROM Table1 ) qGROUP BY GroupID\[/code\]But I'm getting an error that says \[code\]Column 'q.rn' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.\[/code\]Is there a way to do this when CodeNum could have between 1 and 12 values per GroupID?