Tough SQL question - 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)

Tough SQL question

Author

Topic

vladibo
Starting Member

11 Posts

Posted - 2005-05-11 : 15:31:53

OK, so let's say I have table:


CREATE TABLE Test(
gui_id UNIQUEIDENTIFIER DEFAULT newid(),
vch_code VARCHAR(50)
)

INSERT INTO Test (vch_code) VALUES ('A')
INSERT INTO Test (vch_code) VALUES ('B')
INSERT INTO Test (vch_code) VALUES ('C')
INSERT INTO Test (vch_code) VALUES ('C')

How can I get that result (concatinated string of all) WITH A SINGLE QUERY

Result
--------------------
A,B,C,D

I know how to do that with WHILE loop but I really need to get it with a sinle query. And I don't know weather they are 4, 5 or 100 records there.

Thanks in advance.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-05-11 : 15:50:13

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

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

vladibo
Starting Member

11 Posts

Posted - 2005-05-11 : 16:38:41

OK Thanks,

It is then"

DECLARE @vch_list varchar(100)
SELECT @vch_list = COALESCE(@vch_list + ', ', '') + vch_code FROM Test
SELECT @vch_list

But how can I use that thing in INNER JOIN with other table?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-11 : 16:43:05

Just depends on what you want to see. Here's an example:



CREATE TABLE Test(gui_id UNIQUEIDENTIFIER DEFAULT newid(), vch_code char(1))

INSERT INTO Test (vch_code) VALUES ('A')
INSERT INTO Test (vch_code) VALUES ('B')
INSERT INTO Test (vch_code) VALUES ('C')
INSERT INTO Test (vch_code) VALUES ('D')

CREATE TABLE Test2(vch_code char(1), MoreInfo varchar(20))

INSERT INTO Test2 VALUES('A', 'SomeMoreInfoForA')
INSERT INTO Test2 VALUES('B', 'SomeMoreInfoForB')
INSERT INTO Test2 VALUES('C', 'SomeMoreInfoForC')

DECLARE @vch_list varchar(100)
DECLARE @vch_moreinfo varchar(100)

SELECT @vch_list = COALESCE(@vch_list + ',', '') + t1.vch_code, @vch_moreinfo = COALESCE(@vch_moreinfo + ',', '') + t2.MoreInfo
FROM Test t1
INNER JOIN Test2 t2
ON t1.vch_code = t2.vch_code

SELECT @vch_list, @vch_moreinfo

DROP TABLE Test
DROP TABLE Test2

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources