Início / dba / Perguntas / 50811
Accepted
Lumpy
Asked: 2013-10-02 06:10:57 +0800 CST

selecionar dados de linha como lista de colunas

  • 772

Em meu banco de dados, tenho um conjunto de valores de campos personalizados que podem ser configurados pelo cliente. Eles podem criar rótulos para esses campos e atribuir valores que são vinculados a tabelas específicas no banco de dados. Preciso relatar essas informações de forma que os rótulos apareçam como colunas e os valores sejam listados abaixo. Eu poderia fazer isso com uma função PIVOT, exceto que as informações nos valores não são necessariamente agregáveis. (Estou inventando novas palavras) Estou usando o seguinte código para teste.

create table custlabel (custlabelid int identity(1,1) not null, custlabel varchar(50))
CREATE table custvalue (custvalueid int identity(1,1) not null, custlabelid int not null, custvalue varchar(50), linkageid int not null)
create table linkage (linkageid int identity(1,1), blah varchar(50))

INSERT into linkage(blah)
values('test'), ('test2'), ('test3')

INSERT INTO custlabel(custlabel)
values('info1'), ('info2'), ('info3'), ('info4'), ('info5')

INSERT INTO custvalue(custlabelid, custvalue, linkageid)
VALUES(1, 'info1 value1', 1),
(1, 'info1 value2', 1),
(1, 'info1 value3', 1),
(2, 'info2 value1', 1),
(2, 'info2 value2', 1),
(2, 'info2 value3', 1),
(2, 'info2 value4', 1),
(3, 'info3 value1', 1),
(3, 'info3 value2', 1),
(3, 'info3 value3', 1),
(4, 'info4 value1', 1)

O que eu preciso ver voltar é

blah, info1, info2, info3, info4, info5
test, info1 value1, info2 value1, info3 value1, NULL, NULL
test, info1 value2, info2 value2, info3 value2, NULL, NULL
test, info1 value3, info2 value3, info3 value3, NULL, NULL
test, info4 value1, NULL, NULL, NULL, NULL
sql-server-2008-r2 pivot

1 respostas

  1. Best Answer

    Você ainda deve conseguir obter o resultado desejado usando a função PIVOT . Se os valores que você precisa agregar forem uma string, você usaria minou maxpara obter o resultado final.

    Com base em seus dados de amostra e no resultado desejado, você precisará gerar um valor distinto que será usado ao agrupar no PIVOT. Eu usaria row_number()para criar um número sequenciado para cada um blahe custlabelisso permitirá que você retorne várias linhas para cada um blah.

    Se você tiver um número limitado de custlabels, poderá codificar a consulta:

    select blah, info1, info2, info3, info4, info5
from
(
  select cl.custlabel,
    cv.custvalue,
    l.blah
    , row_number() over(partition by l.blah, cl.custlabel
                      order by cl.custlabel) seq
  from custlabel cl
  inner join custvalue cv
    on cl.custlabelid = cv.custlabelid
  inner join linkage l
    on cv.linkageid = l.linkageid
) d
pivot
(
  max(custvalue)
  for custlabel in (info1, info2, info3, info4, info5)
) piv;

    Consulte SQL Fiddle com demonstração .

    Mas se você vai ter um número desconhecido de custlabels, então você vai querer usar o SQL dinâmico para obter o resultado final:

    DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(custlabel) 
                    from custlabel
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT blah, ' + @cols + ' 
            from 
            (
              select cl.custlabel,
                cv.custvalue,
                l.blah
                , row_number() over(partition by l.blah, cl.custlabel
                                  order by cl.custlabel) seq
              from custlabel cl
              inner join custvalue cv
                on cl.custlabelid = cv.custlabelid
              inner join linkage l
                on cv.linkageid = l.linkageid
            ) x
            pivot 
            (
                max(custvalue)
                for custlabel in (' + @cols + ')
            ) p '

execute sp_executesql @query;

    Consulte SQL Fiddle com demonstração

    Ambas as versões darão um resultado:

    | BLAH |        INFO1 |        INFO2 |        INFO3 |        INFO4 |  INFO5 |
| test | info1 value1 | info2 value1 | info3 value1 | info4 value1 | (null) |
| test | info1 value2 | info2 value2 | info3 value2 |       (null) | (null) |
| test | info1 value3 | info2 value3 | info3 value3 |       (null) | (null) |
| test |       (null) | info2 value4 |       (null) |       (null) | (null) |
    • 2

relate perguntas