Technical Article

get all values separated by commas

,

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:

  1. First you have to declare a string.
  2. Then you can concatenate all the rows into the string,
  3. 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

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

2.17 (12)

You rated this post out of 5. Change rating

Share

Tags

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

2.17 (12)

You rated this post out of 5. Change rating