PersonId   EyeColor    HairColor    FavoriteTVShow      FavoriteMovie    FavoriteFood    BirthCity
---------- ----------- ------------ ------------------- ---------------- --------------- ------------------
A123       Brown       Black        The Price Is Right     The Godfather Pizza           New York City
A444       Green       Brown        Friends                Elf           Steak           Seattle

PersonId POS         ColorInfo FavoriteInfo       BirthCity
-------- ----------- --------- ------------------ -------------
A123     1           Brown     The Price Is Right New York City
A123     2           Black     The Godfather      NULL
A123     3           NULL      Pizza              NULL
A444     1           Green     Friends            Seattle
A444     2           Brown     Elf                NULL
A444     3           NULL      Steak              NULL

So for each PersonId, each group of columns (2 ColorInfo, 3 FavoriteInfo, and 1 BirthCity) is transposed and a position field is added. I've tried variations of CROSS APPLY and UNPIVOT, but I can't seem to get it just right. I know it's a weird request, but can anyone think of a good way to do this?

--Create the table
CREATE TABLE #test (
PersonId VARCHAR(10),
EyeColor VARCHAR(20),
HairColor VARCHAR(20),
FavoriteTVShow VARCHAR(30),
FavoriteMovie VARCHAR(30),
FavoriteFood VARCHAR(30),
BirthCity VARCHAR(30)
)

--Populate the table
INSERT INTO #test
VALUES
('A123', 'Brown', 'Black', 'The Price Is Right', 'The Godfather', 'Pizza', 'New York City'),
('A444', 'Green', 'Brown', 'Friends', 'Elf', 'Steak', 'Seattle')

--See the original result set.
SELECT *
FROM #test
 

--My desired result set
SELECT *
FROM ( VALUES ('A123', 1, 'Brown', 'The Price Is Right', 'New York City'),
              ('A123', 2, 'Black', 'The Godfather', NULL),
              ('A123', 3, NULL, 'Pizza', NULL),
              ('A444', 1, 'Green', 'Friends', 'Seattle'),
              ('A444', 2, 'Brown', 'Elf', NULL),
              ('A444', 3, NULL, 'Steak', NULL) ) t (PersonId, POS, ColorInfo, FavoriteInfo, BirthCity)

PersonId   EyeColor    HairColor    FavoriteTVShow      FavoriteMovie    FavoriteFood    BirthInfo
---------- ----------- ------------ ------------------- ---------------- --------------- ------------------
A123       Brown       Black        The Price Is Right  NULL             Pizza           New York City
A444       NULL        Brown        Friends             Elf              Steak           Seattle

PersonId ColorInfo FavoriteInfo       BirthInfo
-------- --------- ------------------ -------------
A123     Brown     The Price Is Right New York City
A123     Black     Pizza              NULL
A444     Brown     Friends            Seattle
A444     NULL      Elf                NULL
A444     NULL      Steak              NULL

So for each PersonId, each group of columns (2 ColorInfo cols, 3 FavoriteInfo cols, and 1 BirthInfo col) is transposed into rows. I've tried variations of CROSS APPLY and UNPIVOT, but I can't seem to get it just right. I know it's a weird request, but can anyone think of a good way to do this?

--Create the table
CREATE TABLE #test (
PersonId VARCHAR(10),
EyeColor VARCHAR(20),
HairColor VARCHAR(20),
FavoriteTVShow VARCHAR(30),
FavoriteMovie VARCHAR(30),
FavoriteFood VARCHAR(30),
BirthInfo VARCHAR(30)
)

--Populate the table
INSERT INTO #test
VALUES
('A123', 'Brown', 'Black', 'The Price Is Right', NULL, 'Pizza', 'New York City'),
('A444', NULL, 'Brown', 'Friends', 'Elf', 'Steak', 'Seattle')

--See the original result set.
SELECT *
FROM #test

--My desired result set
SELECT *
FROM ( VALUES ('A123', 'Brown', 'The Price Is Right', 'New York City'),
              ('A123', 'Black', 'Pizza', NULL),
              ('A444', 'Brown', 'Friends', 'Seattle'),
              ('A444', NULL, 'Elf', NULL),
              ('A444', NULL, 'Steak', NULL) ) t (PersonId, ColorInfo, FavoriteInfo, BirthInfo) 

So for each PersonId, each group of columns (2 ColorInfo, 3 FavoriteInfo, and 1 BirthCity) is transposed and a position field is added. I've tried variations of CROSS APPLY and UNPIVOT, but I can't seem to get it just right. I know it's a weird request, but can anyone think of a good way to do this?

So for each PersonId, each group of columns (2 ColorInfo, 3 FavoriteInfo, and 1 BirthCity) is transposed and a position field is added. I've tried variations of CROSS APPLY and UNPIVOT, but I can't seem to get it just right. I know it's a weird request, but can anyone think of a good way to do this?