Show Multiple rows of data in line line

[Zofia Kow]

I have data in 2 tables (master-detail concept) I want to present results in the query with all details in one row. I'm working in SQL 2005, I do not know number of det per master... Please help...
Data:
Master Table: Details Table:
ID FF1 ID FFd1 FFd2
1 User1 1 DetB DetA
2 User2 1 DetC DetH
2 DetA DetC
2 DetD DetF
2 DetG DetH
Expected result:
1 User1 DetB DetA DetC DetH
2 User2 DetA DetC DetD DetF DetG DetH

Note - thanks to CK9663 for answer re CSV file - it works OK..

[ck9663]

Here, read this.

Happy Coding!!!


~~ CK

[Rabbit]

Use the FOR XML PATH in a subquery to string it together.

SELECT masterID,
   (
      SELECT detailField + ' '
      FROM detailTable AS d
      WHERE d.foreignID = m.masterID
      FOR XML PATH('')
   ) AS strungTogether
FROM masterTable AS m

[Zofia Kow]

Thanks - it works but it concatenates all details together; I guess I was not explaining the result clearly - it needs to have each det info as a separate column, but in one record:
Max # of detailes is 8

ID User F1 F2 F3 F4 F5 F6 F7 F8
1 User1 DetB DetA DetC DetH null null null null
2 User2 DetA DetC DetD DetF DetG DetH null null

[Zofia Kow]

Auch!! This looks complex..... Any way without PIVOT? I find out there is max 8 details. I do not want to link the files 8 times either...

[Rabbit]

I'm not sure why you don't want to use pivot, it's the preferred method.

You don't have to link it 8 times, just pivot it.

Union the two fields, run a ROW_NUMBER() on it, and then pivot.

[Zofia Kow]

I guess its time to learn the PIVOT.... Thanks for help to both of you...