4

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
    You need PIVOT. – Nick Chammas Jun 11 '12 at 19:11
  • 1
    Duplicate of: stackoverflow.com/q/10986031/877069 Please don't cross-post. – Nick Chammas Jun 11 '12 at 19:43
  • Yeah, I actually posted there inadvertantly, then realized it should be here. By the time I got back there to delete it it had a correct answer! Sorry! – Devil's Advocate Jun 11 '12 at 19:44
  • No problem. Since you got an answer there already, please delete this post. – Nick Chammas Jun 11 '12 at 19:46
  • I kinda wanted it here in case someone else finds it useful, especially since it already has 2 upvotes. But I don't want to give myself credit for answering (I posted the answer, but I won't accept it). Can we "close" this question instead of deleting? – Devil's Advocate Jun 11 '12 at 19:47
2

Answered by Bluefeet in https://stackoverflow.com/questions/10986031/sql-dynamic-view-with-column-names-based-on-column-values-in-source-table

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)

Not the answer you're looking for? Browse other questions tagged or ask your own question.