Search code examples
pythonsql-servert-sqlsql-server-2014sql-server-2017

Split single column into multiple columns in SQL server based on number of records


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.


Solution

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

    Example on DB Fiddle