15,567,466 members
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
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
Herman<T>.Instance 1-Oct-15 9:18am
use pivot() and Row_number() functions

Solution 2

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
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!