Get Comma Separated List from Query
-
Can anyone tell me how to write a sql query to get a comma separated list of values. Eg. IF the table ColorTable contains in it's colors field red,blue and green. The query "Select colors from ColorTable" would return 3 rows with each row containing each color. I would like to have a query that would return the colors in one row as red,blue,green instead of returning 3 rows I tried using list(colors) but this function is not recognised. Kindly help me, Thanks in advance
-
Can anyone tell me how to write a sql query to get a comma separated list of values. Eg. IF the table ColorTable contains in it's colors field red,blue and green. The query "Select colors from ColorTable" would return 3 rows with each row containing each color. I would like to have a query that would return the colors in one row as red,blue,green instead of returning 3 rows I tried using list(colors) but this function is not recognised. Kindly help me, Thanks in advance
-
select ( (select description from colortable where description='green') + ',' + (select description from colortable where description='Blue') + ','+ (select description from colortable where description='Red') ) as colors from colortableIs there any other way without hard coding the values and individually concatenating them as I have to retrieve data from a table with numerous records based on some conditions.
-
Can anyone tell me how to write a sql query to get a comma separated list of values. Eg. IF the table ColorTable contains in it's colors field red,blue and green. The query "Select colors from ColorTable" would return 3 rows with each row containing each color. I would like to have a query that would return the colors in one row as red,blue,green instead of returning 3 rows I tried using list(colors) but this function is not recognised. Kindly help me, Thanks in advance
You can do something like this:
declare @list varchar(1000) select @list = isnull(@list, '') + colors + ',' from ColorTable select @listIt's got some extra "features" like that it always ends in a comma, but for every issue you may have with it there's a reasonable solution. Ask if you need more help with that. -
You can do something like this:
declare @list varchar(1000) select @list = isnull(@list, '') + colors + ',' from ColorTable select @listIt's got some extra "features" like that it always ends in a comma, but for every issue you may have with it there's a reasonable solution. Ask if you need more help with that.That was just what i needed. Thanks a lot. By using the following i have removed the comma that appears at the end of the line select substring(@list,1,len(@list)-1) Now can you please tell me if there is a way in which the comma before the last item can be replaced with 'or' i.e. I would like the output to be like red,blue,green,..........pink or yellow -- modified at 0:55 Thursday 8th June, 2006
-
That was just what i needed. Thanks a lot. By using the following i have removed the comma that appears at the end of the line select substring(@list,1,len(@list)-1) Now can you please tell me if there is a way in which the comma before the last item can be replaced with 'or' i.e. I would like the output to be like red,blue,green,..........pink or yellow -- modified at 0:55 Thursday 8th June, 2006
Sure, try this:
declare @list varchar(1000) declare @len int select @list = isnull(@list, '') + colors + ',', @len = len(colors) from ColorTable select @list = substring(@list, 1, len(@list)-1) select substring(@list, 1, len(@list) - @len - 1) + " or " + substring(@list, len(@list) - @len, @len)I'm not in front of a SQL Server right now so I can't try it easily, and I may be off by a character (and thus a -1 or +1 in the substring lengths) here and there. I'm sure you can figure out the tweaks, though. The concept is to have a variable hold the length of the last element. It does this by constantly replacing its value with the length of the current element; at the end of the query, it will contain the length of the last element. Once you know that, it's a matter of string handling to cut your list up and replace text as necessary. Two more things: 1. If there's a possibility that ColorTable may contain zero, one or two rows, or if you're using a where clause that may return only zero, one or two rows, you may want either some if/then or case when clauses with select count statements to ensure you don't return errors. 2. While it's possible to do this in SQL, as demonstrated, there are some things that are better left to code. I assume you have a really good reason for doing it on the SQL Server instead of in whatever language you're using for your queries, but in general I'd recommend this be done in a code library instead of on the DB server. -
Sure, try this:
declare @list varchar(1000) declare @len int select @list = isnull(@list, '') + colors + ',', @len = len(colors) from ColorTable select @list = substring(@list, 1, len(@list)-1) select substring(@list, 1, len(@list) - @len - 1) + " or " + substring(@list, len(@list) - @len, @len)I'm not in front of a SQL Server right now so I can't try it easily, and I may be off by a character (and thus a -1 or +1 in the substring lengths) here and there. I'm sure you can figure out the tweaks, though. The concept is to have a variable hold the length of the last element. It does this by constantly replacing its value with the length of the current element; at the end of the query, it will contain the length of the last element. Once you know that, it's a matter of string handling to cut your list up and replace text as necessary. Two more things: 1. If there's a possibility that ColorTable may contain zero, one or two rows, or if you're using a where clause that may return only zero, one or two rows, you may want either some if/then or case when clauses with select count statements to ensure you don't return errors. 2. While it's possible to do this in SQL, as demonstrated, there are some things that are better left to code. I assume you have a really good reason for doing it on the SQL Server instead of in whatever language you're using for your queries, but in general I'd recommend this be done in a code library instead of on the DB server.Thanks a lot for your help. Thanks a lot for taking the time to reply.
-
Sure, try this:
declare @list varchar(1000) declare @len int select @list = isnull(@list, '') + colors + ',', @len = len(colors) from ColorTable select @list = substring(@list, 1, len(@list)-1) select substring(@list, 1, len(@list) - @len - 1) + " or " + substring(@list, len(@list) - @len, @len)I'm not in front of a SQL Server right now so I can't try it easily, and I may be off by a character (and thus a -1 or +1 in the substring lengths) here and there. I'm sure you can figure out the tweaks, though. The concept is to have a variable hold the length of the last element. It does this by constantly replacing its value with the length of the current element; at the end of the query, it will contain the length of the last element. Once you know that, it's a matter of string handling to cut your list up and replace text as necessary. Two more things: 1. If there's a possibility that ColorTable may contain zero, one or two rows, or if you're using a where clause that may return only zero, one or two rows, you may want either some if/then or case when clauses with select count statements to ensure you don't return errors. 2. While it's possible to do this in SQL, as demonstrated, there are some things that are better left to code. I assume you have a really good reason for doing it on the SQL Server instead of in whatever language you're using for your queries, but in general I'd recommend this be done in a code library instead of on the DB server.Thanks.It's just what I needed.