No need to
PIVOT - a combination of
ROW_NUMBER and
GROUP BY will do the job:
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; ...
Submit an article or tip