Need Help with a select statement

• blast2hell

  Right there with Babe

  Points: 771

  More actions

  January 17, 2008 at 10:20 am

  #185694

  Ok, I'm building a stored procedure that pulls information on customer accounts....I need to return one row per account.

  However, these accounts can be flagged and these flags are stored in another table...so I want to return the flag associated with each account, the problem is there can be more then one flag on an account.

  SELECT

  Accounts.Acct_ID,

  Flags.FlagTitle

  FROM

  Accounts

  LEFT JOIN Flags ON Accounts.Acct_ID = FLags.Acct_ID

  the statement above will naturally return more then one row per account if there is more then one flag per account. There is no limit on the number of flags that can be attached to an account

  so Ideally my return would be

  AcctID|Flag1|Flag2|Flag3....

  Or even

  AcctID|Flags

  Where flags is just one varchar field with all the flag values seperated by commas in a string

  any ideas?

• Mark Cowne

  One Orange Chip

  Points: 26865

  More actions

  January 17, 2008 at 10:27 am

  #769326

  Assuming you're using SQL Server 2005

  SELECT

  a.Acct_ID,

  STUFF((SELECT ','+f.FlagTitle AS "text()"

  FROM Flags f

  WHERE a.Acct_ID = f.Acct_ID

  ORDER BY f.FlagTitle

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

  FROM Accounts a

  ____________________________________________________

  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