Início / dba / Perguntas / 122665
Accepted
MrVimes
Asked: 2015-12-03 05:53:21 +0800 CST

Não dinâmico com linhas convertidas em colunas, bem como colunas convertidas em linhas

  • 772

Eu tentei entender exemplos não dinâmicos na web, mas eles não resultam exatamente no que estou tentando fazer, que é ter linhas específicas transformadas em colunas e colunas transformadas em linhas. Para tentar ilustrar o que quero dizer, incluí uma foto...

insira a descrição da imagem aqui

Eu acredito que os dados em uma determinada coluna precisam ser do mesmo tipo, então se for esse o caso, tudo bem ter todas as colunas como varchar. Os dados na tabela de origem não precisarão fazer parte de nenhuma soma, apenas exibidos como estão. Estou apenas tentando 'girar' isso para exibir os dados de forma vertical para consumo do usuário final.

sql-server-2005 t-sql

1 respostas

  1. Best Answer

    Esta consulta usa tanto UNPIVOT quanto PIVOT:

    SELECT piv.[Data], piv.x, piv.y
FROM (
    SELECT [Type], ColA = CAST(ColA as varchar(10)), ColB = CAST(ColB as varchar(10)), ColC = CAST(ColC as varchar(10)), ColD = CAST(ColD as varchar(10))
    FROM @data
) d
UNPIVOT (
    [value] FOR [Data] IN (ColA, ColB, ColC, ColD)
) as unpiv
PIVOT (
    MAX([value]) 
    FOR [Type] IN ([x], [y])
) as piv
;
    • Ele primeiro não dinamiza seus dados para obter uma tabela regular
    • Em seguida, ele gira esta tabela de volta para o novo formato necessário

    Eu tenho que lançar tudo como varchar. Com base em seu modelo de dados, talvez seja necessário convertê-los em outro tipo. Uma função agregada é exigida pelo PIVOT. Como esta é uma partida de 1 para 1, ela funciona com MAX ou MIN.

    Com SQL Server >= 2008 você também pode substituir UNPIVOT por Table Value Constructor e CROSS APPLY junto com PIVOT :

    SELECT piv.[Data], piv.x, piv.y
FROM (
    SELECT v.[Type], [value], [Data]
    FROM @data d
    CROSS APPLY (values
        (d.[type], CAST(d.ColA as varchar(10)), 'ColA')
        , (d.[type], CAST(d.ColB as varchar(10)) , 'ColB')
        , (d.[type], CAST(d.ColC as varchar(10)), 'ColC')
        , (d.[type], CAST(d.ColD as varchar(10)), 'ColD')
    ) as v([type], [value], [Data])
) unpiv
PIVOT (
    MAX([value])
    FOR [Type] IN ([x], [y])
) as piv
;

    Sem PIVOT / UNPIVOT você pode usar esta consulta com qualquer versão (antiga) do SQL Server:

    SELECT [Data]
    , [x] = MAX(CASE WHEN [type] = 'x' THEN [value] END)
    , [y] = MAX(CASE WHEN [type] = 'y' THEN [value] END)
FROM (
    SELECT [type], [value] = CAST(ColA as varchar(10)), [Data] = 'ColA' FROM @data
    UNION ALL
    SELECT [type], [value] = CAST(ColB as varchar(10)), [Data] = 'ColB' FROM @data
    UNION ALL
    SELECT [type], [value] = CAST(ColC as varchar(10)), [Data] = 'ColC' FROM @data
    UNION ALL
    SELECT [type], [value] = CAST(ColD as varchar(10)), [Data] = 'ColD' FROM @data
) as v
GROUP BY [Data]

    Resultado:

    Data    x       y
ColA    123456  654321
ColB    $500    $200
ColC    6       36
ColD    30      90

    Dados:

    Declare @data table([Type] char(1), ColA bigint, ColB varchar(10), ColC int, ColD int);
INSERT INTO @data([Type], ColA, ColB, ColC, ColD) VALUES
    ('x', 123456, '$500', 6, 30)
    , ('Y', 654321, '$200', 36, 90);
    • 4

relate perguntas