SQL：动态视图，其列名基于源表中的列值

Given the two sample tables here:

鉴于这里有两个样本表：

Tickets Table

门票表

ID  User    Description

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

Properties Table

属性表

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.

请务必注意，属性表并不总是相同。一些“门票”可能具有其他人没有的属性。

Is this even possible?

这有可能吗？

1 个解决方案

#1

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:

您可以使用PIVOT执行此操作。在执行PIVOT时，您可以使用以下两种方法之一：使用Static Pivot编写要转换的行，或使用Dynamic Pivot在运行时创建列列表：

Static Pivot (See SQL Fiddle for Demo):

Static Pivot（请参阅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

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

或者您可以使用Dynamic Pivot（请参阅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.

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

#1