How to convert values ​​from one column to a comma separated list?

Question

Hi, I am using SQLServer2008.

My table has one "Code" field.

When I write a request

select Code from Table1 

then it gives lower output

 Code ---- 9 8 7 6 5 10 31 

and my required result:

 ["9","8","7","6","5","10","31"] 

I have about 14,000 code in a table. How can I get this result? Thanks.

Answer 1

Try this option -

 DECLARE @temp TABLE(Code INT) INSERT INTO @temp (Code) VALUES (9),(8),(7),(6),(5),(10),(31) SELECT STUFF(( SELECT ', "' + CAST(Code AS VARCHAR(10)) + '"' FROM @temp FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '[') + ']' 

Output:

 ["9", "8", "7", "6", "5", "10", "31"] 

Answer 2

Here is your request:

 DECLARE @var varchar(8000) SET @var = '[' SELECT @var = @var + '"' + convert(varchar(100),Code) + '",' FROM Table1 SET @var = SUBSTRING(@var,0,LEN(@var)) + ']' SELECT @var 

SUBSTRING(@var,0,LEN(@var)) removes the unnecessary comma at the end of the line

Answer 3

 try this DECLARE @Str VARCHAR(MAX) SELECT @Str = COALESCE(@Str + ',', '') + '"'+CAST(Code AS VARCHAR(50))+'"' FROM Table1 SELECT '[' +@Str +']' 

Answer 4

try it

 declare @formattdstring varchar(100) set @formattdstring='' select @ formattdstring=@formattdstring +'"'+ Code +'",' from Table1 select '[' +@formattdstring +']' as formattedstring 

Hooray!!!