Database Administrators Asked on January 8, 2021
I have a table that looks like the following:
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
My desired result set is this:
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?
Thank you in advance!
Here’s the T-SQL:
--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)
I think that this will give you what you are looking for. You have to hop through a few CTE's to unpivot the data, then add some attribute information and then pivot it back, but the output does match what you provided...
I had to add a CTE_RawData to convert everything to the same data type since the unpivot doesn't like mismatches...
DROP TABLE IF EXISTS #test
--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')
;WITH CTE_RawData AS
(
SELECT PersonID
, CONVERT(NVARCHAR(30), EyeColor) AS EyeColor
, CONVERT(NVARCHAR(30), HairColor) AS HairColor
, CONVERT(NVARCHAR(30), FavoriteTVShow) AS FavoriteTVShow
, CONVERT(NVARCHAR(30), FavoriteMovie) AS FavoriteMovie
, CONVERT(NVARCHAR(30), FavoriteFood) AS FavoriteFood
, CONVERT(NVARCHAR(30), BirthInfo) AS BirthInfo
FROM #Test AS T
)
, CTE_UP AS
(
SELECT PersonID
, Attribute
, [Value]
FROM CTE_RawData AS T
UNPIVOT ([Value] FOR Attribute IN (EyeColor, HairColor, FavoriteTVShow, FavoriteMovie, FavoriteFood, BirthInfo)) AS unpvt
)
, CTE_UPRN AS
(
SELECT PersonID
--, Attribute
, [Value]
, AttributeCategory = CASE WHEN Attribute IN ('EyeColor', 'HairColor') THEN 'ColorInfo'
WHEN Attribute IN ('FavoriteTVShow', 'FavoriteMovie', 'FavoriteFood') THEN 'FavoriteInfo'
WHEN Attribute IN ('BirthInfo') THEN 'BirthInfo'
ELSE Attribute
END
, RN = ROW_NUMBER() OVER (PARTITION BY PersonID, CASE WHEN Attribute IN ('EyeColor', 'HairColor') THEN 'ColorInfo'
WHEN Attribute IN ('FavoriteTVShow', 'FavoriteMovie', 'FavoriteFood') THEN 'FavoriteInfo'
WHEN Attribute IN ('BirthInfo') THEN 'BirthInfo'
ELSE Attribute
END
ORDER BY PersonID)
FROM CTE_UP AS U
)
SELECT PersonID
, RN
, ColorInfo
, FavoriteInfo
, BirthInfo
FROM CTE_UPRN AS U
PIVOT (MAX([Value]) FOR AttributeCategory IN (ColorInfo, FavoriteInfo, BirthInfo)) AS pvt
ORDER BY PersonID
, RN
Correct answer by Jonathan Fite on January 8, 2021
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP