get all values separated by commas

akashb42u, 2012-07-19 (first published: 2012-07-03)

Just for fun, i have to get all values from a table, into an 'array' like, all values separated with commas.

Some guys told me to use a cursor or an while ... loop.

The mission is only a concatenate, why i have loop it?

What you have to do is:

First you have to declare a string.
Then you can concatenate all the rows into the string,
and finnaly you have to cut the tips.

Simple?

SELECT is like a loop that you can do some stuff on each row.

Some one told that if you do SELECT @i=id FROM XPTO

you will get the last id, that is almost wrong. You get the last because you are setting, on each row, a new vaue to @i.

But if we always keep the value of @i we can get all the records in a row.

Have fun TSQLing.

--Creating a test table create table teste (id varchar(10)) --Insert values for testing Insert into teste ('T1') Insert into teste ('T2') Insert into teste ('T3') --Now do what matters --declare the 'array' like variable declare @i as nvarchar(max) set @i='' -- the first value must be '', cannot be NULL -- fill with values select @i=@i + ''''+ id +''',' from teste -- cut the last comma set @i = left(@i, len(@i)-1) -- et voila, Fim já está!!!! select @i --result of select @i is 'T1','T2','T3' -- By Luis Costa, Lisbon - PT

get all values separated by commas

Rate

Share

Share

Rate