Need to divide single column into multiple columns

Question

My table has data with a single column:

1
2
3
4
5
6
7
8
9
10

And I want output like this:

1 2 3 4 5-first row
6 7 8 9 10 - second row

Another has two columns like this:

Column 1 Column 2
1 a
2 b
3 c
4 d
5 e
6 f
7 g
8 h
9 i
10 j

And I want it to display like this:

1 a 2 b 3 c 4 d 5 e-first row
6 f 7 g 8 h 9 i 10 j - second row

Answer 1

For the first case you can use ROW_NUMBER then PIVOT to do this using the modulo operator to divide into columns and the result of integer divsion by 5 to group into rows.

WITH CTE AS
(
SELECT C,
 (ROW_NUMBER() OVER (ORDER BY C) -1)%5 AS Col,
 (ROW_NUMBER() OVER (ORDER BY C) -1)/5 AS Row
FROM YourTable 
)
SELECT [0], [1], [2], [3], [4]
FROM CTE
PIVOT (MAX(C) FOR Col IN ([0], [1], [2], [3], [4])) AS Pvt
ORDER BY Row

SQL Fiddle

For the second case Oracle has a straight forward syntax to achieve this with PIVOT. SQL Server doesn't but you can do this as below.

WITH CTE AS
(
SELECT *,
 (ROW_NUMBER() OVER (ORDER BY Column1) -1)%5 AS Col,
 (ROW_NUMBER() OVER (ORDER BY Column1) -1)/5 AS Row
FROM YourTable 
)
SELECT MAX(CASE WHEN Col = 0 THEN Column1 END),
 MAX(CASE WHEN Col = 0 THEN Column2 END),
 MAX(CASE WHEN Col = 1 THEN Column1 END),
 MAX(CASE WHEN Col = 1 THEN Column2 END),
 MAX(CASE WHEN Col = 2 THEN Column1 END),
 MAX(CASE WHEN Col = 2 THEN Column2 END),
 MAX(CASE WHEN Col = 3 THEN Column1 END),
 MAX(CASE WHEN Col = 3 THEN Column2 END),
 MAX(CASE WHEN Col = 4 THEN Column1 END),
 MAX(CASE WHEN Col = 4 THEN Column2 END)
FROM CTE
GROUP BY Row
ORDER BY Row

SQL Fiddle

• Suresh E: If its have two column means how to perform this opertaion?
• Martin Smith: @SureshSurya - Please edit your question to include example data and desired results for this case.
• Roi Gavish: @MartinSmith Great answer, I would replace the pseudo SELECT * with SELECT Column1, Column2 for the sake of good habit... :)
• Martin Smith: @Justicator - I used to do that but now I'm more relaxed about * in places where it does not make any difference. In the first query in my answer it would make a difference as any new columns added to YourTable would silently be added to the implicitly defined GROUP BY of the PIVOT likely causing the query to return incorrect results. In the second case even if a new column was added SQL Server wouldn't unnecessarily retrieve it. It recognises at compile time that only Column1 and Column2 would be used.
• Roi Gavish: @MartinSmith You are correct of course, But... I work with programmers and find it easier to "force" them to always use column names than to make them think before and decide each time. I also think that not everyone who reads an answer here understands when it makes no difference and why (well, now they know...), therefor it was just "for the sake of good habit". This is definitely not to lessen from your really great answer.