I have around 28 records (which is dynamic, that is sometimes odd numbers of records and sometime even) in a single column.
Would like to split those into multiple columns based on a variable. There should be 5 or 6 records in each column.
Note: These 5 or 6 records is a variable and should be changed.
Example input table:
Col1
-----
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z
The required output table will be:
Col1 col2 col3 col4 col5
-------------------------------------
A F K P U
B G L Q V
C H M R W
D I N S X
E J O T Y
Z
Here is what I have tried and these two different query is providing different results:
select Col1
from Table1 where Col1 is not null
order by [col1] asc
--This provides all records
WITH CTE AS
(
SELECT [Col1],
(ROW_NUMBER() OVER (ORDER BY Col1) -1)%5 AS Col,
(ROW_NUMBER() OVER (ORDER BY Col1) -1)/5 AS Row
FROM Table1 where Col1 is not null
)
SELECT [0], [1], [2], [3], [4]
FROM CTE
PIVOT (MAX([COL1]) FOR Col IN ([0], [1], [2], [3], [4])) AS Pvt
ORDER BY Row
-- In this case records are missing (when number of records is high, as number of records can increase dynamically)
WITH CTE AS
(
SELECT [Col1],
(ROW_NUMBER() OVER (ORDER BY Col1) -1)%5 AS Col,
(ROW_NUMBER() OVER (ORDER BY Col1) -1)/5 AS Row
FROM Table1 where Col1 is not null
)
SELECT [0], [1]
FROM CTE
PIVOT (MAX([COL1]) FOR Col IN ([0], [1])) AS Pvt
ORDER BY Row
What am I doing wrong? How can I achieve the desired output? Thanks.
You need to first allocate a column to each record, which can be done with NTILE()
:
WITH Table1 AS
(
SELECT TOP 26 Col1 = CHAR(64 + ROW_NUMBER() OVER(ORDER BY object_id))
FROM sys.all_objects
)
SELECT [Col1],
5 - NTILE(5) OVER(ORDER BY Col1 DESC) AS Col
FROM Table1 ;
Giving:
Col1 Col
---------
Z 4
Y 4
X 4
W 4
V 4
U 4
T 3
....
Y 4
Z 4
Note, to start filling from the last column you have to allocate the columns in reverse order (ORDER BY Col1 DESC
), then deduct this from total number of columns.
Then you can work out your row by ordering within your columns:
WITH Table1 AS
(
SELECT TOP 26 Col1 = CHAR(64 + ROW_NUMBER() OVER(ORDER BY object_id))
FROM sys.all_objects
)
SELECT Col1, Col, ROW_NUMBER() OVER(PARTITION BY Col ORDER BY Col1) AS Row
FROM ( SELECT [Col1],
5 - NTILE(5) OVER(ORDER BY Col1 DESC) AS Col
FROM Table1
) c;
Giving:
Col1 Col Row
--------------------
A 0 1
B 0 2
C 0 3
D 0 4
E 0 5
F 1 1
G 1 2
Then you can apply the pivot:
WITH Table1 AS
(
SELECT TOP 26 Col1 = CHAR(64 + ROW_NUMBER() OVER(ORDER BY object_id))
FROM sys.all_objects
), CTE AS
(
SELECT Col1, COL, ROW_NUMBER() OVER(PARTITION BY Col ORDER BY Col1) AS Row
FROM ( SELECT [Col1],
5 - NTILE(5) OVER(ORDER BY Col1 DESC) AS Col
FROM Table1
) c
)
SELECT [0], [1], [2], [3], [4]
FROM CTE
PIVOT (MAX([COL1]) FOR Col IN ([0], [1], [2], [3], [4])) AS Pvt
ORDER BY Row;
Giving:
0 1 2 3 4
-------------------------------------
A F K P U
B G L Q V
C H M R W
D I N S X
E J O T Y
NULL NULL NULL NULL Z
ADDENDUM
I have set up a sample table, and used a procedure with dynamic SQL for ease of re-use to demonstrate this solution, it seems to work as expected for me.
SET UP
-- SET UP TABLE AND INSERT RANDOM VALUES
IF OBJECT_ID(N'tempdb..#Table1', 'U') IS NOT NULL
DROP TABLE #Table1;
CREATE TABLE #Table1 (Col1 CHAR(2));
INSERT #Table1 (Col1)
SELECT CONCAT(Letter, Number)
FROM (SELECT TOP 26 Letter = CHAR(64 + ROW_NUMBER() OVER(ORDER BY object_id))
FROM sys.all_objects) l
CROSS JOIN (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9)) n (Number);
GO
IF OBJECT_ID(N'tempdb..#GenerateMatrix', 'P') IS NOT NULL
DROP PROCEDURE #GenerateMatrix;
GO
CREATE PROCEDURE #GenerateMatrix @Records INT, @Columns INT
AS
BEGIN
-- GENERATE COLUMNS FOR PIVOT AND SELECT
DECLARE @ColSQL NVARCHAR(MAX) =
STUFF((SELECT TOP (@Columns)
CONCAT(',', QUOTENAME(ROW_NUMBER() OVER(ORDER BY Col1) - 1))
FROM #Table1
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '');
-- FOR @Cols = 5 Generates "[0],[1],[2],[3],[4]"
DECLARE @SQL NVARCHAR(MAX) =
CONCAT('SELECT ', @ColSQL, '
FROM (SELECT Col1, Col, Row = ROW_NUMBER() OVER(PARTITION BY Col ORDER BY Col1)
FROM ( SELECT [Col1], Col = @Columns - NTILE(@Columns) OVER(ORDER BY Col1 DESC)
FROM (SELECT TOP (@Records) Col1 FROM #Table1 Col1) t
) c) c
PIVOT (MAX([COL1]) FOR Col IN (', @ColSQL, ')) AS Pvt
ORDER BY Row;');
EXECUTE sp_executesql @SQL, N'@Columns INT, @Records INT', @Columns = @Columns, @Records = @Records;
END
GO
TEST 1
EXECUTE #GenerateMatrix @Records = 26, @Columns = 5;
0 1 2 3 4
----------------------------------
A1 A6 B2 B7 C3
A2 A7 B3 B8 C4
A3 A8 B4 B9 C5
A4 A9 B5 C1 C6
A5 B1 B6 C2 C7
NULL NULL NULL NULL C8
TEST 2
EXECUTE #GenerateMatrix @Records = 8, @Columns = 4;
0 1 2 3
----------------------------
A1 A3 A5 A7
A2 A4 A6 A8
TEST 3
EXECUTE #GenerateMatrix @Records = 40, @Columns = 8;
0 1 2 3 4 5 6 7
-----------------------------------------------------------
A1 A6 B2 B7 C3 C8 D4 D9
A2 A7 B3 B8 C4 C9 D5 E1
A3 A8 B4 B9 C5 D1 D6 E2
A4 A9 B5 C1 C6 D2 D7 E3
A5 B1 B6 C2 C7 D3 D8 E4
TEST 4
EXECUTE #GenerateMatrix @Records = 50, @Columns = 6;
0 1 2 3 4 5
---------------------------------------
A1 A9 B8 C7 D6 E6
A2 B1 B9 C8 D7 E7
A3 B2 C1 C9 D8 E8
A4 B3 C2 D1 D9 E9
A5 B4 C3 D2 E1 F1
A6 B5 C4 D3 E2 F2
A7 B6 C5 D4 E3 F3
A8 B7 C6 D5 E4 F4
NULL NULL NULL NULL E5 F5
There are no records missing, and no rows with only null values in any of the tests.