Master SQL Query & CSV for Child tables

• Manish Joisar

  Ten Centuries

  Points: 1173

  More actions

  January 23, 2008 at 9:21 pm

  #180785

  I have 2 tables

  Master

  -------

  Field1

  Field2

  Child

  -----

  Field1

  Field2

  MasterField1

  I want a single query which give results something like

  Master Child

  ----------------------------------------------------

  Master1 Child1, Child2, Child3

  Master2 Child1, Child2, Child3, Child4

  Master3 Child1, Child2

  & So on

  There are 3 posibility to generate above data

  1. Create User Defined function pass master key & return string

  2. Declare cursor loop around master table

  Generate child string

  Append to temp table

  3. Create Temp Table,

  Apped Master data with identity field

  Loop around temp table (Cursor not reqd in this case)

  Generate child string

  Append to another temp table

  If anybody have other than above, let me know

  Is there any possibility where in single Query i can return data something like above?

  As i am concern for performance, if i used any of the above solution.

  Which will have minimum performance hit?

  Thanks in advance

• Mark Cowne

  One Orange Chip

  Points: 26944

  More actions

  January 24, 2008 at 5:01 am

  #771213

  Another option is to use FOR XML PATH

  SELECT m.Field1 AS Master,

  STUFF((SELECT ','+c.Field1 AS "text()"

  FROM Child c

  WHERE c.MasterField1=m.Field1

  ORDER BY c.Field1

  FOR XML PATH('')),1,1,'') AS Child

  FROM Master m

  ORDER BY m.Field1

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537