SQL: Dynamic view with column names based on column values in source table
sql, sql-server, sql-server-2005
Solution
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:
Static Pivot (See 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):
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.
Problem
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?