Início / dba / Perguntas / 281069
Accepted
Mike S
Asked: 2020-12-08 14:17:25 +0800 CST

Como posso transpor várias colunas não relacionadas em linhas no SQL Server?

  • 772

Tenho uma tabela que se parece com a seguinte:

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

Meu conjunto de resultados desejado é este:

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

Assim, para cada PersonId, cada grupo de colunas (2 colunas ColorInfo, 3 colunas FavoriteInfo e 1 coluna BirthInfo) é transposto em linhas. Eu tentei variações de CROSS APPLYe UNPIVOT, mas não consigo acertar. Eu sei que é um pedido estranho, mas alguém pode pensar em uma boa maneira de fazer isso?

Agradeço antecipadamente!

Aqui está o 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 respostas

  1. Best Answer

    Eu acho que isso vai te dar o que você está procurando. Você precisa passar por alguns CTEs para desdinamizar os dados, adicionar algumas informações de atributo e girá-los de volta, mas a saída corresponde ao que você forneceu ...

    Eu tive que adicionar um CTE_RawData para converter tudo para o mesmo tipo de dados, pois o unpivot não gosta de incompatibilidades ...

    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

relate perguntas