Create a view with dynamic column headers based on original table row data? 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?
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)