Geek asks and answers

SQL single column divided into multiple columns

I want to split the result of a single column into 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
+5
source share
2 answers

To obtain deterministic results, you need to have a unique column ORDER BY, but something in this direction 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
+8
source

Here you have many options, look for one that is more suitable for your business: Create columns from a list of values

Previous link to even more information.

+1

More articles:


All Articles