5

I want to split the single column result from a query into 4 columns:

Sample source: (Select [FirstName from User)
Peter
Mary
John
Tina
Carl
Jane
Bill
Sarah

I want to look like this: 

Column1   Column2   Column3   Column4  
Peter     Mary      John      Tina  
Carl      Jane      Bill      Sarah
Cœur
  • 37,241
  • 25
  • 195
  • 267
callisto
  • 4,921
  • 11
  • 51
  • 92

2 Answers2

8

You need to have a unique column to ORDER BY to get deterministic results but something along these lines should work.

;WITH T
     AS (SELECT [FirstName],
                ( ROW_NUMBER() OVER (ORDER BY (SELECT 0)) - 1 ) / 4 AS Row,
                ( ROW_NUMBER() OVER (ORDER BY (SELECT 0)) - 1 ) % 4 AS Col
         FROM   [User])
SELECT [0] AS Column1,
       [1] AS Column2,
       [2] AS Column3,
       [3] AS Column4
FROM   T PIVOT (MAX(name) FOR Col IN ([0], [1], [2], [3])) P 
ORDER BY Row
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • Thank you! I did get it working using 4 temp tables with differing Modulus and joining on rownums, but the CTE looks nicer! – callisto Jul 09 '12 at 08:54
  • @NikolaMarkovinović - True, or the aliasing needs changed as it outputs `1,2,3,0,1,2,3,0`. Edited as per your suggestion. – Martin Smith Jul 09 '12 at 08:55
1

Here you have tons of options, look for the one is more suited for your case: Create columns from list of values

Previous link directs to even more information

Yaroslav
  • 6,476
  • 10
  • 48
  • 89
  • I had a look at the link, but it only shows how to build a single column containing multiple rows' values. I want to break down 1 column into 4 columns – callisto Jul 09 '12 at 08:34
  • Yes, you are right, I just edited my answer, maybe it should be a comment and not a full answer as the provided links give you lot of info but not the exact answer to your question. – Yaroslav Jul 09 '12 at 08:36