6

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?

Improve this question
4
  • 3
    Property tables are the devil. Commented Jun 11, 2012 at 19:10
  • 1
    @Jeremy so can hard-coding property names as column names. Both have their place. Commented Jun 11, 2012 at 19:12
  • @AaronBertrand, once again I don't disagree with you... but every time I have to use property tables I almost always have performance issues, and they are usually a pain in the butt to pivot. Commented Jun 11, 2012 at 19:15
  • 3
    @Jeremy but that does not necessarily represent every time for everyone - I've used EAV with great success in the past, I even blogged about it because so many people have this blanket opinion that it's such a terrible thing. sqlblog.org/blogs/aaron_bertrand/archive/2009/11/19/… It's like cursors. Are they bad in general? Yes. Are they always bad? Absolutely not. Commented Jun 11, 2012 at 19:17

1 Answer 1

Reset to default
18

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

your first example is considerably more complicated than mine with the exact same results, and the second one... dynamic sql? Before going that route, I think you'd want to make darn sure that's a requirement and how the table is going to be consumed.
You are my new hero. I want to have your babies. (Dynamic Pivot works EXACTLY how I need)
@JeremyHolovacs I disagree that the first is more complicated than your version with 3 left joins. As far as the dynamic version, the OP requested a dynamic version of a query which would be flexible and that is what I provided. There are always other ways to answer a question, feel free to suggest another dynamic way. :)
This is very slick. We'd love to have you post on DBA.SE. (As a side note, you can remove the TYPE and .value() from the XML query. The call to .value() is unnecessary without the TYPE.)
But you can't use dynamic sql in a view, can you?