select

[Lafor]

Hello Guys/Gals

Would like to select 5 items from a table

ids
1
2
3
4
5

and concatenate them to return
1 2 3 4 5

any ideas? Could that be done in 1 select statement?

Thanks in advance...

[RoyceWindsor1]

I think this will work, but I have to believe there's a better way to do what you're trying to do...

-- Untested...
SELECT 'idstring' = a.id + b.id + c.id + d.id + e.id
FROM
myTable as a
LEFT JOIN
myTable as b
ON
b.id = 2
LEFT JOIN
myTable as c
ON
c.id = 3
LEFT JOIN
myTable as d
ON
d.id = 4
LEFT JOIN
myTable as e
ON
e.id = 5
WHERE
a.id = 1

[RoyceWindsor1]

Actually, you'll have to do a CONVERT on each of those id's before you can concatenate them

[Lafor]

5 was just a number picked at random.... It could have been 2000

I'd like to do this (if possible without using a cursor)

Thanks

[Hack]

You want to select potentially 2000 separate items from one table, and concatenate the results into one string????

[Lafor]

Thanks guys,

I actually figured it out...

[RoyceWindsor1]

How did you do it?

[Hack]

I'm curious. What did you wind up doing?

[Lafor]

1. Using a cursor
2. With no cursor

1.
declare alltogether cursor for
select id from test

declare @concatenatevals varchar(4000)
set @concatenatevals = ' '
declare @ID int

open alltogether
fetch next from alltogether into @ID
while (@@Fetch_Status = 0)
begin
set @concatenatevals = @concatenatevals + ' ' + convert(varchar(10),@ID)
fetch next from alltogether into @ID
end


print @concatenatevals
close alltogether
deallocate alltogether

2.

declare @moi varchar(255)
select @moi = ' '
SELECT @moi = COALESCE(@moi + convert(varchar(10),id), " ") + " "
FROM test
print @moi


granted I may have a little extra in the second solution
Thanks