Click here to Skip to main content
15,567,466 members
Please Sign up or sign in to vote.
2.78/5 (3 votes)
See more:
I have data as below :
Name 
 1
 2
 3
 4
 5
 6
 7
 8
 9


I Require above data as:
A  B  C
1  2  3
4  5  6
7  8  9


For every 3 rows it has to break .
Posted
Updated 1-Oct-15 12:02pm
v3
Comments
Leo Chapiro 1-Oct-15 8:52am    
But you don't have any "ABC" in your source data but "Name"?
Joemens 1-Oct-15 8:57am    
Just any Default alias header names .
aarif moh shaikh 1-Oct-15 8:55am    
use Pivot and Unpivot
Joemens 1-Oct-15 8:57am    
I tried , Can you please help me with query ?
Herman<T>.Instance 1-Oct-15 9:18am    
use pivot() and Row_number() functions

1 solution

No need to PIVOT - a combination of ROW_NUMBER and GROUP BY will do the job:
SQL
WITH cteSortedData As
(
    SELECT
        Name,
        ROW_NUMBER() OVER (ORDER BY Name) - 1 As RN
    FROM
        YourTable
)
SELECT
    MAX(CASE RN % 3 WHEN 0 THEN Name END) As A,
    MAX(CASE RN % 3 WHEN 1 THEN Name END) As B,
    MAX(CASE RN % 3 WHEN 2 THEN Name END) As C
FROM
    cteSortedData
GROUP BY
    RN / 3
;

The expression RN / 3 uses integer division, so each group of three rows returns the same value.
Eg: 0 / 3 = 0; 1 / 3 = 0; 2 / 3 = 0; 3 / 3 = 1; 4 / 3 = 1; ...

% is the Modulo operator[^], which returns the remainder of a division operation.
Eg: 0 % 3 = 0; 1 % 3 = 1; 2 % 3 = 2; 3 % 3 = 0; 4 % 3 = 1; ...
 
Share this answer
 
v2
Comments
CHill60 1-Oct-15 11:47am    
5'd. You beat me to it, I was going to post an almost identical solution ... but without the explanation :)
Maciej Los 1-Oct-15 17:00pm    
Agree!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900