Just to backup Maggo's solution... the method he used is called a "CROSSTAB". You could also use a PIVOT although the CROSSTAB solution is usually a touch faster. Such a solution is one of the better ones because it only has to make a pass at the table one time. To wit, it's a common solution for "pivoting" EAV tables of this nature.
--Jeff Moden
Change is inevitable... Change for the better is not.
Well done Magoo; I knew there was a much better way but drew a blank.
Jeff Moden (9/16/2013)
Just to backup Maggo's solution... the method he used is called a "CROSSTAB". You could also use a PIVOT although the CROSSTAB solution is usually a touch faster. Such a solution is one of the better ones because it only has to make a pass at the table one time. To wit, it's a common solution for "pivoting" EAV tables of this nature.
I wish I had read Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/url] before putting my query together.
-- Itzik Ben-Gan 2001
Alan.B (9/17/2013)
Well done Magoo; I knew there was a much better way but drew a blank.Jeff Moden (9/16/2013)
Just to backup Maggo's solution... the method he used is called a "CROSSTAB". You could also use a PIVOT although the CROSSTAB solution is usually a touch faster. Such a solution is one of the better ones because it only has to make a pass at the table one time. To wit, it's a common solution for "pivoting" EAV tables of this nature.I wish I had read Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/url] before putting my query together.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply