multiple rows as comma separated list

[BDC2]

Hi,

I have 3 tables:
T_Template containing a list of templates
T_Field containing a list of fields
T_Template_Fields containing a list of many field IDs against the Template ID

If I try this:

select FieldID from T_Template_Fields where templateid = 1

I get 5 rows returned containing 5 Field IDs.

The output that I'm after is just one row containing a comma separated string of these 5 Field IDs, can anyone help me?

BDC.

 

[r937]

faq183-2146
faq183-6466
faq183-6485

r937.com | rudy.ca

 

[cmmrfrds]

If you are using SQL Sever 2005 or above then check out the "With Clause" where you can use recursion to build this type of list in Standard SQL.

This site has some good examples.

http://www.eggheadcafe.com/articles/sql_server_recursion_with_clause.asp