SQL tough question

[vladibo]

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.

 

[vongrunt]

Something like:

declare @blah varchar(100); set @blah = ''
select @blah = @blah + vch_code from Test
-- order by something
print @blah

If vch_code can be NULL, wrap it into COALESCE() or ISNULL().

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[vladibo]

Thank you vongrunt,

It can be 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?

 

[vongrunt]

Use INNER JOIN as usual.

Or maybe you want to join 1:n table and display concatenated values from child table for each parent?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[vladibo]

I need this:

SELECT * FROM Test2 t2
INNER JOIN Test3 t3 ON t2.id=t2.test2_id
INNER JOIN (
SELECT * FROM (
DECLARE @vch_list varchar(100)
SELECT @vch_list = COALESCE(@vch_list + ', ', '') + vch_code FROM Test
SELECT @vch_list
) as t
) ON .../* something */

But that won't work

 

[vongrunt]

Sorry, this doesn't explain almost anything. Wanna JOIN on /* something */ there must be some key involved.

Can you explain exactly what you want to do, if possible with some sample data and expected results?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)