Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

closed as off topic by JNK Jun 11 '12 at 19:54

Questions on Database Administrators Stack Exchange are expected to relate to database administration within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

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! –  Scott Beeson 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? –  Scott Beeson Jun 11 '12 at 19:47

1 Answer 1

up vote 0 down vote accepted

Answered by Bluefeet in http://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)
share|improve this answer

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