SQL : Generate string of values in column

Question

I have a silly problem in sql
I had a column with some values in

All i want to convert data in column into a string like

Declare Str as nvarchar(MAX)
Set Str = {Some Query};

I HAD EDITED MY QUESTION
Where Str is something like "[73],[74],[75],[76],[77],[78],[79],[80],[81]" What query should i made for doing this
Please suggest me.

Can you rephrase your question? It is not very clear. Also, the best thing would be to post what you have in the column and what is your desired output. — Radu Gheorghiu, Dec 04 '13 at 06:19
You can do this with the FOR XML command You can refer to this [link][1] [1]: http://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server — kiks73, Dec 04 '13 at 06:19

score 1 · Accepted Answer · answered Dec 04 '13 at 06:37

Try

DECLARE @Str AS NVARCHAR(MAX)
SET @Str = STUFF(
(
  SELECT ',' + QUOTENAME([Column])
    FROM table1 
     FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
SELECT @Str list

Output:

|                                         LIST |
|----------------------------------------------|
| [73],[74],[75],[76],[77],[78],[79],[80],[81] |

Here is SQLFiddle demo

score 1 · Answer 2 · answered Dec 04 '13 at 06:38

You will need to select the data into a cursor and then loop through each row at a time and build your string. Probably best to write a stored procedure for this.

Something like

DECLARE #cTmp CURSOR LOCAL FAST_FORWARD FOR 
SELECT myCol FROM myTable

OPEN #cTmp
FETCH NEXT FROM #cTmp INTO @MyVariableForTheColumn

WHILE @@FETCH_STATUS = 0
BEGIN
  --Build the string
  SET @String = @String + @MyVariableForTheColumn + ', '

 FETCH NEXT FROM #cTmp INTO @MyVariableForTheColumn
END
CLOSE #cTmp

DEALLOCATE #cTmp

score -1 · Answer 3 · answered Dec 04 '13 at 06:43

-1

declare @s varchar(max) = ''

select @s += '[' + some_col + '],' from some_table

print @s

answered Dec 04 '13 at 06:43

gordy

9,360
1
31
43

the query is correct, I just made a sqlfiddle to verify - what error are you getting? http://www.sqlfiddle.com/#!3/8c69d/1/0 – gordy Dec 04 '13 at 06:49
@gordy do not forget to add @ to his name. otherwise will not know about your reply – Hawk Dec 04 '13 at 07:01

SQL : Generate string of values in column

3 Answers3