Return Comma separated values SQL

Question

I have a field names DAILYREPORT.WEATHERCONDITION which can hold values as '1,2,3' or '1' or '2,4' based on what the user selects from the list of weather check boxes available to him. The weather table contains the list of weathers which he selects

Weather Table

ID  Condition
----------

1   Sunny    
2   Cloudy
3 Fine    
4 Windy

Now i need a query which returns the conditions as 'Sunny,Cloudy,Fine' when DAILYREPORT.WEATHERCONDION=1,2,3

http://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server — Darshan Mehta, Dec 17 '13 at 10:04

score 5 · Answer 1 · answered Dec 17 '13 at 10:31

5

Try this :

SELECT STUFF
(
     (select ',' + Condition
      from
      Weather
      where 
      ID in (1,2,3)
      FOR XML PATH('')
      ),1,1,''
)

answered Dec 17 '13 at 10:31

Krishna

170
7

Christos · Answer 2 · 2013-12-17T11:08:24.573

2

DECLARE @list VARCHAR(MAX)
SELECT @list = COALESCE(@list+',' ,'') + Condition
FROM Weather
WHERE ID IN (1,2,3)
SELECT @list

You declare a @list variable of varchar type. Then using the COALESCE expression (please look here http://msdn.microsoft.com/en-us/library/ms190349.aspx for further details on how it works) you get what you want.

That's a SQL fiddle that show that the above works as it is expected

http://sqlfiddle.com/#!6/65df2/1

Note : In order to avoid any misconception, I don't say that the COALESCE solves the stated problem.

It is is only there to deal with initializing the string and the issue of a extra comma at the end.

as Mikael wrote below.

edited Dec 17 '13 at 11:08

answered Dec 17 '13 at 10:04

Christos

53,228
8
76
108

1

I don't think the downvote was because of the code. The code works. The description on how it works is however wrong. Coalesce has nothing to do with concatenating strings. Concatenating strings is done with `+` and this code uses an undocumented (as far as I know) feature that the variable is updated for each row in the select statement. `coalesce` is only there to deal with initializing the string and the issue of a extra comma at the end. http://sqlfiddle.com/#!6/65df2/3 – Mikael Eriksson Dec 17 '13 at 11:00
@Mikael thanks for your comment, because I was confused by the downvote. The downvoter should have left a comment, in order to make better my answer or delete it. However, I don't state anywhere above that COALESCE solves the problem. Thanks again ! – Christos Dec 17 '13 at 11:05

score 2 · Answer 3 · answered Dec 17 '13 at 12:10

mine is same as krishna,

Declare @Weather Table (ID int, Condition varchar(50))
insert into @Weather values(1,'Sunny'),(2,'Cloudy'),(3,'Fine'),(4,'Windy')

select top 1
stuff((select ','+Condition from @Weather b where id in(1,2,3) for xml path('')),1,1,'')Condition

 from @Weather

score 0 · Answer 4 · answered Dec 17 '13 at 11:14

0

Try this i hope it is useful to Your

select SUBSTRING(
(select ','+  s.condition from DAILYREPORT s where id in (1,2,3) order by condition for xml path('')),2,200000) as CSV

answered Dec 17 '13 at 11:14

code save

1,054
1
9
15

Return Comma separated values SQL

4 Answers4

Linked