Complicated Query - 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

SQL Server Development (2000)

Complicated Query

Author

Topic

benko
Starting Member

24 Posts

Posted - 2004-10-28 : 15:50:08

How would i do this. I need to be able to do this with selecting all records from the tables. Thanks in advance.

Table as of now:

ID Person
--- -------
0001 Bill
0001 Bob
0002 Ken
0002 Ryan

How i would to convert to this with table above:

ID Person
--- -------
0001 Bill, Bob
0002 Ken, Ryan

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-10-28 : 16:10:42

You could use a user defined function that would build a CSV string using the Coaleasce function. Search for the article on this site that explains it.

benko
Starting Member

24 Posts

Posted - 2004-10-28 : 16:24:00

Like i get how this works if i specified it for instance just one record(id). But if i bring back like 10000 records how would i do it. I guess my prob is im just not seeing how the select statement would look for it. Can ya help me out?!

Declare @ColumnList varchar(1000)
SELECT @ColumnList = COALESCE(@ColumnList + ', ', '') + rs.rsCompany

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-28 : 16:27:18

Here's the article:

http://www.sqlteam.com/item.asp?ItemID=11021

Tara

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-28 : 21:32:02

Here is an intersting way of doing this:


CREATE FUNCTION dbo.concatStr
(
	@curStr varchar(4000),
	@newStr varchar(100)
)  
RETURNS varchar(4000) AS  
BEGIN 
	Return (isnull(@curStr+',','')+@newStr)
END
Go

Declare @myTable table (id int, name varchar(100), list varchar(4000))
Insert Into @myTable
Select 1, 'Bill', null
Union Select 1, 'Bob', null
Union Select 2, 'Ken', null
Union Select 2, 'Ryan', null

Declare @curStr varchar(4000),
	@key varchar(4000)

Update @myTable
Set 
	@curStr = list = case when @key = convert(varchar,id) then dbo.concatStr(@curStr,name) else name end,
	@key = id
From @myTable

Select id, list 
From @myTable A
where len(list) = (select max(len(list)) from @myTable where id = A.id)
go

Drop function dbo.concatStr

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources