Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

1 Answer 1

up vote 4 down vote accepted

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

share|improve this answer
    
Thanks for the instant help....its really working fine....thanks you once again –  Suresh E Jun 15 '13 at 19:21
    
If its have two column means how to perform this opertaion? –  Suresh E Jun 16 '13 at 7:33
    
@SureshSurya - Please edit your question to include example data and desired results for this case. –  Martin Smith Jun 16 '13 at 9:11
    
Thanks once again for your help...and it is very useful for me... –  Suresh E Jun 16 '13 at 11:53
    
@MartinSmith Great answer, I would replace the pseudo SELECT * with SELECT Column1, Column2 for the sake of good habit... :) –  Justicator Jun 16 '13 at 13:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.