Geek asks and answers

SQL: dynamic view with column names based on column values ​​in the source table

For two example tables:

Ticket table

ID  User    Description

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

Property 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 come to this form:

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 property table will not always be the same. Some Tickets may have properties that others do not.

Is it possible?

+5
source share
1 answer

You can accomplish this with PIVOT . When doing PIVOT, you can do this in one of two ways: using Static Pivot, you will encode rows for conversion or Dynamic Pivot that will create a list of columns at runtime:

(. SQL Fiddle ):

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

Dynamic Pivot (. SQL Fiddle ):

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)

.

+11

More articles:


All Articles