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 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 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 
SELECT * with SELECT Column1, Column2 for the sake of good habit... :) * 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.