主页 / dba / 问题 / 50811
Accepted
Lumpy
Asked: 2013-10-02 06:10:57 +0800 CST

选择行数据作为列列表

  • 772

在我的数据库中，我有一组可由客户设置的自定义字段值。他们可以为这些字段创建标签，然后分配链接回数据库中特定表的值。我需要能够报告此信息，以便标签显示为列并且值在下面列出。我可以使用 PIVOT 函数来做到这一点，只是值中的信息不一定是可聚合的。（我正在编新词）我正在使用以下代码进行测试。

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)

我需要看到的是

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 个回答

  1. Best Answer

    您仍然应该能够使用PIVOT函数获得所需的结果。如果您需要聚合的值是一个字符串，那么您将使用minormax来获得最终结果。

    根据您的样本数据和所需的结果，您将需要生成一个独特的值，该值将在 PIVOT 中进行分组时使用。我将使用row_number()为每个blah和创建一个序列号custlabel，这将允许您为每个返回多行blah。

    如果您的 数量有限custlabels，则可以对查询进行硬编码：

    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;

    请参阅SQL Fiddle with Demo。

    但是，如果您将拥有未知数量的custlabels，那么您将需要考虑使用动态 SQL 来获得最终结果：

    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;

    请参阅带有演示的 SQL Fiddle

    两个版本都会给出结果：

    | 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

相关问题