Subquery with multiple results

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Subquery with multiple results

Author

Topic

GoodTogether
Starting Member

5 Posts

Posted - 2007-11-14 : 08:05:56

Hi,

I have two tables which have a relationship as follows

TABLE1
-------
Table1Key
Table1Field

TABLE2
-------
Table2Key
Table1ForeignKey
Table2Field

The relationship is a 1-many between Table1.Table1Key and Table2.Table1ForeignKey

I want to run a query that pulls all rows from Table1 with a new field that pulls in the all associated values from Table2.Table2Field separated by commas.

So, something along the lines of this (the bit in purple is the bit I want to return as 1 field, but seperate by commas!)


SELECT 
 t1.[Table1Field]
 (SELECT t2.[Table2Field] FROM [Table2] t2 AS CommaSeperatedField)
FROM
 [Table1] t1

Hope I've been clear enough!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-14 : 08:08:18

see Rowset string concatenation

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-14 : 08:09:18

quote:
I want to run a query that pulls all rows from Table1 with a new field that pulls in the all associated values from Table2.Table2Field separated by commas.

i think OP wanted a csv string

KH
[spoiler]Time is always against us[/spoiler]

GoodTogether
Starting Member

5 Posts

Posted - 2007-11-14 : 08:12:23

khtan - thanks for that. It looks just the trick!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-14 : 08:19:17

Or with SQL Server 2005 methods
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

E 12°55'05.25"
N 56°04'39.16"