how to convert rows values to a single string

Question

I have a select statement and returns one column but many rows. I want to convert that to a single string with commas like below.

 SELECT CODE FROM LG_xxx_CLCARD WHERE CODE < 'KUR' AND CODE > 'CARI1'

this returns

 ASD1
 ASD2
 ASD3

I want to convert this to

 'ASD1','ASD2','ASD3'

How can I do this in SQL?

Your example results do not meet the `where` conditions, at least for most collations. — Gordon Linoff, Dec 09 '14 at 13:12

How 'bout a Fresca · Accepted Answer · 2014-12-09T14:01:37.163

4

Use the STUFF function:

SELECT STUFF(
    (SELECT ',' + CODE 
     FROM LG_xxx_CLCARD 
     WHERE CODE < 'KUR' AND CODE > 'CARI1' 
     ORDER BY CODE 
     FOR XML PATH ('')), 1, 1, '')

If you need the single quotes, then the following will do the trick:

SELECT STUFF(
    (SELECT ',' + '''' + CODE + '''' 
     FROM LG_xxx_CLCARD 
     WHERE CODE < 'KUR' AND CODE > 'CARI1' 
     ORDER BY CODE 
     FOR XML PATH ('')), 1, 1, '')

edited Dec 09 '14 at 14:01

answered Dec 09 '14 at 13:13

How 'bout a Fresca

2,267
1
15
26

This doesn't include the single quotes. – Gordon Linoff Dec 09 '14 at 13:26

score 1 · Answer 2 · answered Dec 09 '14 at 13:21

1

Try this:

SELECT LISTAGG(CODE , ',') WITHIN GROUP (ORDER BY CODE ) FROM LG_xxx_CLCARD WHERE CODE < 'KUR' AND CODE > 'CARI1'

The result should be

'ASD1','ASD2','ASD3', ..

answered Dec 09 '14 at 13:21

Wayne

11
1

`list_agg()` is not a SQL Server function. – Gordon Linoff Dec 09 '14 at 13:25

score 0 · Answer 3 · answered Dec 09 '14 at 13:16

0

declare @tmp varchar(250)
SET @tmp = ''
select @tmp = @tmp + CODE + ', ' from LG_xxx_CLCARD 
                          WHERE CODE < 'KUR' AND CODE > 'CARI1'

answered Dec 09 '14 at 13:16

HaveNoDisplayName

8,291
106
37
47

how to convert rows values to a single string

3 Answers3