Click here to Skip to main content
15,063,287 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 11:02am
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
Joemens 1-Oct-15 10:01am
   
I'm new to sql , can you please give me a query for it ?

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; ...
   
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