put them together - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

put them together

Author

Topic

willbourne
Starting Member

7 Posts

Posted - 2005-10-27 : 03:46:08

Can someone help me with this? I want to collect the au_lname cells and put them together when having the same state. I will use the solution on another db but used pubs to get help easier.

BR
Markus

This is how I want it to look like:
state au_lname
CA White, Green, Carson...
IN DeFrance
KS Smith

From the result of this query:
USE pubs
SELECT au.state, au.au_lname
FROM authors au
ORDER BY state

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-10-27 : 07:42:01

use a function for this:


CREATE FUNCTION dbo.LastNames
(
	@state varchar(2)
)  
RETURNS varchar(8000)
AS  
BEGIN 
	declare @List varchar(8000)
	SELECT 	@List = COALESCE(@List + ', ', '') + au_lname
	FROM 	authors 
	WHERE  	state = @state
	return	@List
END
go 
select 	state, dbo.LastNames(state)
FROM 	authors 
group by state
order by state

go
drop function dbo.LastNames

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-28 : 05:51:25

Refer this on why you need function to do this

http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

Madhivanan

Failing to plan is Planning to fail

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-10-28 : 07:32:18

Maddy, you could simply post this line from your link, instead of posting the link:

"Yeah, yeah, yeah, let's get this out of the way right from the start: Don't concatenate rows into delimited strings in SQL Server. Do it client side."

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-28 : 07:36:33

>>"Yeah, yeah, yeah, let's get this out of the way right from the start: Don't concatenate rows into delimited strings in SQL Server. Do it client side."

Thats good idea Don

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources