Pivot Numbered List to Multiple Rows of Set Number of Columns-CodePudding

My problem is as follows. I have a table like this:

And I am trying to turn it into something like this:

The intent is to collapse the list of names into one or more rows of 4 names each. If a row has less than 4 names, the remaining name fields must be NULL.

I have been told I should be using a PIVOT for this, but I am having trouble getting it to work.

How should this be accomplished? To be fully transparent, there are multiple columns I would like to do this with, but I'm starting simple. The original table would have extra data columns and I would like to end with something like:

Quickstart:

DROP TABLE IF EXISTS #tmpTest

CREATE TABLE #tmpTest (
    CreatedAt Date,
    [Index] INT,
    [Name] VARCHAR(32)
)

INSERT INTO #tmpTest ([CreatedAt], [Index], [Name])
VALUES('2022-07-20',    1,  'David'),
('2022-07-20',  2,  'Haley'),
('2022-07-20',  3,  'John'),
('2022-07-20',  4,  'Mark'),
('2022-07-20',  5,  'Matt'),
('2022-07-20',  6,  'Sarah'),
('2022-08-13',  1,  'David'),
('2022-08-13',  2,  'Haley'),
('2022-08-13',  3,  'John')

CodePudding user response：

unpivot with values() and calculate the group of 4 and the column name ordinal of 4 by index.

CREATE TABLE #tmpTest (
    CreatedAt Date,
    [Index] INT,
    [Name] VARCHAR(32), Other VARCHAR(20), Another VARCHAR(20)
);

INSERT INTO #tmpTest ([CreatedAt], [Index], [Name], Other, Another)
VALUES('2022-07-20',    1,  'David', 'a', 'b'),
('2022-07-20',  2,  'Haley', 'c', 'd'),
('2022-07-20',  3,  'John', 'e', 'f'),
('2022-07-20',  4,  'Mark', 'g', 'h'),
('2022-07-20',  5,  'Matt', 'i', 'j'),
('2022-07-20',  6,  'Sarah', 'k', 'l'),
('2022-08-13',  1,  'David', 'm', 'n'),
('2022-08-13',  2,  'Haley', 'o', 'p'),
('2022-08-13',  3,  'John', 'q', 'r');


SELECT P.*
FROM (SELECT T.[CreatedAt],U.COL,U.GRP,U.VAL
FROM #tmpTest AS T
CROSS APPLY(VALUES
(CONCAT('Name',1 (T.[index]-1)%4),1 (T.[index]-1)/4,T.Name), 
(CONCAT('Other',1 (T.[index]-1)%4),1 (T.[index]-1)/4,T.Other), 
(CONCAT('Another',1 (T.[index]-1)%4),1 (T.[index]-1)/4,T.Another)
) AS U(COL,GRP,VAL)) AS D
PIVOT(MAX(VAL) FOR COL IN (Name1,Other1,Another1,Name2,Other2,Another2,Name3,Other3,Another3,Name4,Other4,Another4)) AS P;