SQL:具有基于源表中列值的列名的动态视图

问题描述

鉴于这里的两个示例表:

Given the two sample tables here:

门票表

ID  User    Description

0   James   This is a support ticket
1   Fred    This is a ticket too

属性表

ID  TicketID    Label           Value

0   0           Engineer        Scott
1   1           Engineer        Dale
2   0           Manu            Dell
3   1           Manu            HP
4   0           OS              Windows
5   1           OS              Linux

我怎样才能到达这样的视图:

How can I arrive at a view like this:

ID  User    Description                 Engineer    Manu    OS

1   James   This is a support ticket    Scott       Dell    Windows
2   Fred    This is a ticket too        Dale        HP      Linux

需要注意的是，属性表并不总是相同的.一些票"可能具有其他人没有的属性.

It is important to note that the properties table would not always be the same. Some "Tickets" may have properties that others do not.

这甚至可能吗?

推荐答案

您可以使用 枢轴.在执行 PIVOT 时，您可以通过以下两种方式之一进行操作:使用静态 Pivot 对要转换的行进行编码，或者使用 Dynamic Pivot 将在运行时创建列列表:

You can perform this with a PIVOT. When doing the PIVOT you can do it one of two ways, with a Static Pivot that you will code the rows to transform or a Dynamic Pivot which will create the list of columns at run-time:

Static Pivot(参见 SQL Fiddle for Demo):

Static Pivot (See SQL Fiddle for Demo):

select id, [user], [engineer], [manu], [OS]
from 
(
    select t.id
        , t.[user]
        , p.ticketid
        , p.label
        , p.value
    from tickets t
    inner join properties p
        on t.id = p.ticketid
) x
pivot
(
    min(value)
    for label in ([engineer], [manu], [OS])
) p

或者您可以使用动态 Pivot(请参阅 SQL Fiddle for Demo):

Or you can use a Dynamic Pivot (See SQL Fiddle for Demo):

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

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(p.label) 
                    from tickets t
                    inner join properties p
                        on t.id = p.ticketid
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT id, [user], ' + @cols + ' from 
             (
                 select t.id
                        , t.[user]
                        , p.ticketid
                        , p.label
                        , p.value
                    from tickets t
                    inner join properties p
                        on t.id = p.ticketid
            ) x
            pivot 
            (
                min(value)
                for label in (' + @cols + ')
            ) p '

execute(@query)

两个查询将返回相同的结果.

Both query will return the same results.

这篇关于SQL:具有基于源表中列值的列名的动态视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！