主页 / dba / 问题 / 281069
Accepted
Mike S
Asked: 2020-12-08 14:17:25 +0800 CST

如何将多个不相关的列转换为 SQL Server 中的行？

  • 772

我有一个如下所示的表：

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)
sql-server t-sql

1 个回答

  1. Best Answer

    我认为这会给你你正在寻找的东西。您必须跳过几个 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
    • 1

相关问题