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
Improve this question

2 Answers 2

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thank you! I did get it working using 4 temp tables with differing Modulus and joining on rownums, but the CTE looks nicer!
@NikolaMarkovinović - True, or the aliasing needs changed as it outputs 1,2,3,0,1,2,3,0. Edited as per your suggestion.
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

Improve this answer

2 Comments

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
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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.