Retrieve comma separated values

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)

Retrieve comma separated values

Author

Topic

cykophysh
Starting Member

24 Posts

Posted - 2007-04-12 : 09:05:11

Hi guys,

I need to retrieve a comma separated values from the database.
i.e I have a linking table that contains a Branch To Location association, by associating a BranchID to a LocalityID.

My Query Looks like this


Select dbo.LOCALITY.LOCALITY from  dbo.LOCALITY 
				inner join dbo.BranchLocality on  dbo.BranchLocality.LOCALITY_ID =  dbo.LOCALITY.ID
				where dbo.BranchLocality.BRANCH_ID = 34

This returns a Table.
i.e
New York
Alaska
California
Wyoming
Utah

What I would Like to achieve is that I return it as one field with a a Comma Separating the values

i.e
"New York, Alaska, California, Wyoming, Utah" etc

Kind Regards,
Gary

<A href="http://www.threenineconsulting.com" target="_blank" >My Website</a> || <a href="http://threenineconsulting.com/forum/blogs/cykophysh/default.aspx" targer="_blank">My Blog</a>

Kristen
Test

22859 Posts

Posted - 2007-04-12 : 09:09:19

See: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Concatenating+data+from+rows+with+same+GroupBy+column

Kristen

cykophysh
Starting Member

24 Posts

Posted - 2007-04-12 : 09:14:52

Sorry For wasting your time
I found the answer with the help of this article
[url]http://www.sqlteam.com/item.asp?ItemID=2368[/url]

Kind Regards,
Gary

<A href="http://www.threenineconsulting.com" target="_blank" >My Website</a> || <a href="http://threenineconsulting.com/forum/blogs/cykophysh/default.aspx" targer="_blank">My Blog</a>

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-12 : 09:24:01

also see http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-12 : 10:03:59

Try this

SELECT DISTINCT	b.BRANCH_ID,
		STUFF((SELECT DISTINCT TOP 100 PERCENT ',' + l.LOCALITY FROM dbo.LOCALITY AS l WHERE b.LOCALITY_ID = l.ID ORDER BY ',' + l.LOCALITY FOR XML PATH('')), 1, 1, '') AS Localities
FROM		dbo.BranchLocality AS b
ORDER BY	b.BRANCH_ID

Peter Larsson
Helsingborg, Sweden

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-04-12 : 10:37:01

quote:
Originally posted by cykophysh

Hi guys,

I need to retrieve a comma separated values from the database.
i.e I have a linking table that contains a Branch To Location association, by associating a BranchID to a LocalityID.

My Query Looks like this

Select dbo.LOCALITY.LOCALITY from dbo.LOCALITY
inner join dbo.BranchLocality on dbo.BranchLocality.LOCALITY_ID = dbo.LOCALITY.ID
where dbo.BranchLocality.BRANCH_ID = 34

This returns a Table.
i.e
New York
Alaska
California
Wyoming
Utah

What I would Like to achieve is that I return it as one field with a a Comma Separating the values

i.e
"New York, Alaska, California, Wyoming, Utah" etc

Kind Regards,
Gary

<A href="http://www.threenineconsulting.com" target="_blank" >My Website</a> || <a href="http://threenineconsulting.com/forum/blogs/cykophysh/default.aspx" targer="_blank">My Blog</a>

try this with case stmt ...

DECLARE @STR VARCHAR(1000)
SELECT @STR = (CASE WHEN @STR IS NULL THEN <ur field> ELSE @STR + ', ' + <ur field> END)
FROM <ur table>

SELECT
@STR AS CSV_STRING

Mahesh

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-12 : 10:50:30

However I am interested to know where you want to show the concatenated data?

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-04-12 : 17:04:10

"I found the answer with the help of this article
http://www.sqlteam.com/item.asp?ItemID=2368"

Given that this is a SQL 2005 forum you may find that that article is not "safe" for SQL 2005.

I suspect that you need the code that Peso posted, above.

Kristen

mabbj747
Starting Member

2 Posts

Posted - 2010-05-07 : 05:18:50

DECLARE @LocalityList varchar(100)

SELECT @LocalityList = COALESCE(@LocalityList + ', ', '') +
CAST(dbo.LOCALITY.LOCALITYAS varchar(5))
FROM
dbo.LOCALITY
INNER JOIN
dbo.BranchLocality
ON
dbo.BranchLocality.LOCALITY_ID = dbo.LOCALITY.ID
WHERE
dbo.BranchLocality.BRANCH_ID = 34

SELECT @LocalityList

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources