如何將多個不相關的列轉換為 SQL Server 中的行？

我有一個如下所示的表：

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

因此，對於每個 PersonId，每組列（2 個 ColorInfo 列、3 個 FavoriteInfo 列和 1 個 BirthInfo 列）被轉置為行。我嘗試了 and 的變體CROSS APPLY，UNPIVOT但我似乎無法做到恰到好處。我知道這是一個奇怪的要求，但誰能想到一個好方法來做到這一點？

先感謝您！

這是 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)

我認為這會給你你正在尋找的東西。您必須跳過幾個 CTE 才能取消透視數據，然後添加一些屬性資訊，然後將其轉回，但輸出確實與您提供的內容相匹配…

我必須添加一個 CTE_RawData 將所有內容轉換為相同的數據類型，因為 unpivot 不喜歡不匹配…

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

引用自：https://dba.stackexchange.com/questions/281069