TSQL PIVOT AND WRAP

Clayton Coomber 1 Reputation point
2021-11-17T04:06:22.997+00:00

Hi all,

I have a requirement to pivot rows to columns, but i need it to wrap when i've my maximum number of rows. E.g. in the following table i have 6 rows

USE tempdb;
GO
IF OBJECT_ID('T1', 'U') IS NOT NULL 
DROP TABLE T1;
GO

CREATE TABLE T1 (
    ParentId INT,
    ChildId INT,
PRIMARY KEY (
    ParentId,
    ChildId)
);

INSERT INTO T1 
VALUES 
(1, 1),
(1, 2),
(1, 3),
(1, 4),
(1, 5),
(1, 6);

I want a query to return 6 columns and 2 rows - my result should look like this:

USE tempdb;
GO

IF OBJECT_ID(N'Result', 'U') IS NOT NULL
DROP TABLE Result;
GO

CREATE TABLE Result (
ParentId INT,
ChildId_1 INT,
ChildId_2 INT,
ChildId_3 INT,
ChildId_4 INT,
ChildId_5 INT);

INSERT INTO Result
VALUES
(1, 1, 2, 3, 4, 5),
(1, 6, NULL, NULL, NULL, NULL);

SELECT *
FROM Result;

Is this possible?

Thanks for your assistance.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,689 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Viorel 119.7K Reputation points
    2021-11-17T05:51:17.523+00:00

    One of the solutions:

    ;
    with P as
    (
        select *
        from ( select ParentId, ChildId, row_number() over (order by ChildId) n from T1) t
        pivot ( min(ChildId) for n in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10])) p
    ),
    Q as
    (
        select ParentId, [1] as Child_1, [2] as Child_2, [3] as Child_3, [4] as Child_4, [5] as Child_5, 0 as g
        from P
        union all
        select ParentId, [6], [7], [8], [9], [10], 1 as g
        from P
        where [6] is not null
    )
    select * 
    from Q
    order by ParentId, g
    

    Remove 'where [6] is not null' to show the second row even if it contains nulls only (assuming that all of ChildId are never null).

    0 comments No comments

  2. EchoLiu-MSFT 14,601 Reputation points
    2021-11-17T05:57:17.703+00:00

    Hi @Clayton Coomber ,

    Welcome to the microsoft TSQL Q&A forum!

    Please also check:

    --Static pivot  
        ;WITH cte  
        as(SELECT *,ROW_NUMBER() OVER(PARTITION BY ParentId ORDER BY ParentId) rr FROM T1)  
          
        SELECT * FROM (SELECT ParentId,ChildId,'ChildId'+'_'+CAST(rr AS VARCHAR(5)) rr  
        FROM cte) c  
        PIVOT (MAX(ChildId) FOR rr IN([ChildId_1],[ChildId_2],[ChildId_3],[ChildId_4],[ChildId_5])) p  
        UNION ALL  
        SELECT *,NULL,NULL,NULL,NULL FROM (SELECT ParentId,ChildId,rr FROM cte) c  
        PIVOT (MAX(ChildId) FOR rr IN([6])) p  
    

    Dynamic pivot:

    DECLARE @sql NVARCHAR(MAX)= ''  
    DECLARE @columns1 NVARCHAR(MAX)= ''  
    DECLARE @columns2 NVARCHAR(MAX)= ''  
      
    SELECT   
      @columns1 += QUOTENAME(TRIM('ChildId'+'_'+CAST(rr AS VARCHAR(5)))) + ','  
    FROM   
     (SELECT ParentId,ChildId,  
     ROW_NUMBER() OVER(PARTITION BY ParentId ORDER BY ParentId) rr FROM T1) t  
    WHERE rr<6  
      
    SELECT   
      @columns2 += QUOTENAME(TRIM('ChildId'+'_'+CAST(rr AS VARCHAR(5)))) + ','  
    FROM   
     (SELECT ParentId,ChildId,  
     ROW_NUMBER() OVER(PARTITION BY ParentId ORDER BY ParentId) rr FROM T1) t  
    WHERE rr>=6  
      
    SET @columns1 = LEFT(@columns1, LEN(@columns1) - 1);  
    SET @columns2 = LEFT(@columns2, LEN(@columns2) - 1);  
      
    Print @columns1  
    Print @columns2  
      
    SET @sql=';WITH cte  
    as(SELECT *,ROW_NUMBER() OVER(PARTITION BY ParentId ORDER BY ParentId) rr FROM T1)  
      
    SELECT * FROM (SELECT ParentId,ChildId,''ChildId''+''_''+ CAST(rr AS VARCHAR(5)) rr  
    FROM cte) c  
    PIVOT (MAX(ChildId) FOR rr IN('+@columns1+')) p  
    UNION ALL  
    SELECT *,NULL,NULL,NULL,NULL FROM (SELECT ParentId,ChildId,''ChildId''+''_''+ CAST(rr AS VARCHAR(5)) rr FROM cte) c  
    PIVOT (MAX(ChildId) FOR rr IN('+@columns2+')) p'  
      
    EXECUTE sp_executesql @sql;  
    

    Output:
    150043-image.png

    If you have any question, please feel free to let me know.
    If the response is helpful, please click "Accept Answer" and upvote it.

    Regards,
    Echo


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  3. Viorel 119.7K Reputation points
    2021-11-17T06:05:33.727+00:00

    An alternative:

    ;
    with N as
    (
        select *, row_number() over (order by ChildId) as rn
        from T1
    ), 
    Q as
    (
        select *
        from ( select ParentId, ChildId, n = rn % 5, g = rn  / 5 from N) t
        pivot ( min(ChildId) for n in ([1], [2], [3], [4], [5])) p
    )
    select ParentId, [1] as Child_1, [2] as Child_2, [3] as Child_3, [4] as Child_4, [5] as Child_5
    from Q
    order by ParentId, g
    
    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.