0

Possible Duplicate:
Pivot Table and Concatenate Columns - SQL Problem

I've got a one to many relationship that I'd like to compress into one row for a data export:

Header Table
IDh ColA
1   Eggs

Detail Table
IDd FK ColB ColC
1   1  Blah Foo
2   1  Qwer Too
...

I'd like:

ColA ColB1 ColC1 ColB2 ColC2 ColBx ColCx
Eggs Blah  Foo   Qwer  Too   ...   ...

What's the best way to do this? SQL Server 2005

Community
  • 1
  • 1
John
  • 2,653
  • 4
  • 36
  • 57
  • 2
    Search for "PIVOT" - there's gotta be **at least a gazillion** duplicates of this on SO already.... – marc_s Dec 01 '11 at 17:13
  • @marc_s - Wow, a ***gazillion***!? :) – MatBailie Dec 01 '11 at 17:15
  • 1
    I think this is the PIVOT with dynamic columns problem? – Jon Egerton Dec 01 '11 at 17:18
  • @JonEgerton like this one [Pivot Table and Concatenate Columns - SQL Problem](http://stackoverflow.com/questions/159456/pivot-table-and-concatenate-columns-sql-problem) – Conrad Frix Dec 01 '11 at 17:21
  • @ConradFrix that's the sort of thing - generating dynamic SQL with a PIVOT in it. – Jon Egerton Dec 01 '11 at 17:23
  • One of these days, my work will upgrade from `SQL 2000`. For now, I'd like to see how to do it without pivots (i.e. the hard way). –  Dec 01 '11 at 17:23
  • 1
    @jp2code you have to use a bunch of MAX/CASE steatements. e.g. `SELECT MAX(Case WHEN ColB = 'Blah' THEN ColC END) AS Blah, MAX(Case WHEN ColB = 'Qwer' THEN ColC END) AS Qwer` but dynamically of course – Conrad Frix Dec 01 '11 at 17:27
  • 1
    @jp2code You can also use a bunch of repeated joins too. `FROM FKTable a left join table b on a.id = b.fk and b.colb ='Blah' left join table c on a.id = c.fk and c.colb = 'Qwer'` – Conrad Frix Dec 01 '11 at 17:32
  • Thanks Conrad Frix! Two (2) +1 for those. –  Dec 02 '11 at 14:52

0 Answers0