I received following question in email : How to create a comma delimited list using SELECT clause from table column?
Answer is to run following script.
USE AdventureWorks GO DECLARE @listStr VARCHAR(MAX) SELECT @listStr = COALESCE(@listStr+',' ,'') + Name FROM Production.Product SELECT @listStr GO
I have previously written a similar article where I have demonstrated this method using three sample examples. You can read the article for further information. SQL SERVER – Creating Comma Separate Values List from Table – UDF – SP
Here are few of the interesting articles related to this blog post:
- SQL SERVER – Split Comma Separated List Without Using a Function
- SQL SERVER – Creating Comma Separate Values List from Table – UDF – SP
- SQL SERVER – SQLCMD to Build Comma Separated String
Simple CSV implementations may prohibit field values that contain a comma or other special characters such as CSV. The CSV file format is not standardized. CSV is a common data exchange format that is widely supported by consumer, business, and scientific applications. CSV formats are best used to represent sets or sequences of records in which each record has an identical list of fields.
Reference : Pinal Dave (https://blog.sqlauthority.com)
109 Comments. Leave new
Hi Pinal ,
There is no need of COALESCE function for this string column with comma sepration. you can do it in this way also without using COALESCE. I did this thing in sql server 2000 and 2005
USE PUBS
GO
DECLARE @Result AS VARCHAR(2000)
SET @Results = ”
SELECT @Results = au_lname +’, ‘+ @Results FROM
authors
SELECT @Results
GO
—————————-And Your Query would be like this—–
USE AdventureWorks
GO
DECLARE @listStr VARCHAR(MAX)
SET @listStr = ”
SELECT @listStr = Name + ‘, ‘ + @listStr
FROM Production.Product
SELECT @listStr
GO
Regards
Shashi Kant chauhan
Hai,
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘‘’.
This error message is displayed when executing this query.
Hello, Pinal
I agree with you, COALESCE is a good idea (and a good function too), but in your script just the started value of the variable prevent a resultset NULL, but if any value from the list is null (in this case the “name” field), the entire resultset become NULL too. I suggest the follow solution:
USE AdventureWorks
GO
DECLARE @listStr VARCHAR(MAX)
SELECT
@listStr =
COALESCE ( COALESCE(@listStr+‘,’ ,”) + Name , @listStr)
FROM Production.Product
SELECT @listStr
GO
I thought these were all good suggestions until I realized it ws only good for one column.
Is there something similar that would do it for all coluns in a table. For example, I create a temp table with all the data I need to Export then I do something like this:
Select
Column_1+’,’+
Column_2+’,’+
Column_3+’,’+
Column_4+’,’+
Column_5+’,’+
Column_6+’,’+
Column_7+’,’+
Column_8
From TableToExport
Any suggestions? Some times I may have 100 or more columsn from various tables.
Thanks
Hi ,
My name is Sushma.
I am new to learning SQL SQRVER (developer).
I need some programs links.
I am new to this so i am faceing lot of problems(even i dont know codding) plz help me.
Thanks
Sushma.
–create or replace function getAuthor(i_deptNo VARCHAR)
— return VARCHAR(4000)
— is
— cursor c_emp is
— select first_Name from employee where id = i_deptNo;
— v_out VARCHAR2(4000);
— begin
— for r_emp in c_emp loop
— if v_out is null then
— v_out:=r_emp.first_Name;
— else
— v_out:=v_out||’, ‘||r_emp.first_Name;
— end if;
— end loop;
— return v_out;
— end
Try it !!!
Kishor singh
I need learnig the QSL-Server also
Need quik help now!
Get job in 3 dayz
You give help me?
Henk, did you get the job? How did you manage with SQL?
Hi,
Can i use comma seperation in Where clause?
Such as where age=40,height=5; etc?
Thanks
Om
I was looking for a way to convert a column into a comma delimited list, and this worked great, thanks!
kerr is it worked for you in sybase
Thanks karthickraj.
This is really great. Thank you.
Is it possible to improve it to include a GROUP BY? I have a table with two columns (ID, Marker). I want to list all of the Markers that exist for each ID as in:
ID Marker_List
————————————–
1 001, 002
300 002
478 008, 985, 212
u can use LISTAGG function or CONNECT WITH function.
Kevin,
In GROUP BY any COLUMN reported must be also in the GROUP BY, hence there is nothing to aggregate.
hi
i have a requirement
*12345678901115100609*22345678901115100609*12355678901115100609*’
this is string in sqlserver..i want first 10 digits as machine code
next 4digits as a time and last 6digits as date..”*” symbol is
neglected
result like:
machine time date
1234567890 1115 100609
2234567890 1115 100609
In the above example i want to pass the table name as parameter.
can anybody help me to do this.
thanks
Nagendra
Hi Freinds,
I have one Query is,
I have one Table. It has a colum Named RoleID. in this column i have records like ‘3,4,6’. and my query is if i want to select value for ‘4’.
For Example: In Table I have 5 records. in RoleID column like this
ID Name RoldeID
——————————-
1 Mr.A 2
2 Mr.B 2,5
3 Mr.C 3,4
4 Mr.D 2,3,5
5 Mr.E 3,5
Now I want to select Records where RoleId = 3.
when i pass select query like this,
Select * from table where RoleID = 3. then it is not getting any data.
I m New in SQL please Help.
Thanks,
Mazhar
Got solution? I am also having same problem…
Since the column datatype is a VARCHAR type and you have combination of values, please use like operator.
Select * from table where RoleID like ‘%3%’
I think this will not work accurately. This will return the records e.g. roleid 33 ,333,113 and so on
Select * from table where CHARINDEX(‘3’,RoleID)>0
Hi to All,
if you add an ORDER BY clause, and if it’s order by a varchar column there is a problem. Example:
USE AdventureWorks
GO
DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+’,’ ,”) + Name
FROM Production.Product
ORDER BY COLUMN_NAME
SELECT @listStr
GO
If a COLUMN_NAME is a VARCHAR type, result of a query is not comma separated list but a single item. In your example, it would be a single name without a comma. If a COLUMN_NAME is a date or int type, it works just fine. What’s the catch?
This works great
What if you have a (,) within a string, and you just want
to separate the each side into it’s own column.
Example:
12345678,abcdefgh
How can this be done?
Hi Vikram,
You can do this as:
DECLARE @str VARCHAR(100)
SELECT @str = ‘12345678,abcdefgh’
SELECT @str,
SUBSTRING(@str, 0 , CHARINDEX(‘,’,@str)) AS [1stColunmn],
SUBSTRING(@str, CHARINDEX(‘,’,@str) + 1, LEN(@str)) AS [2ndColumn]
Thanks,
Tejas
hai
Could any one help me how to get rid of my problem
My problem is:
table name: countryID
accid localaccid names
———————————————–
101 222 india
102 333 india
103 444 india
201 777 usa
202 888 usa
————————————————
I need like this
names localaccid accid
————————————————–
india 222,333,444 101,102,103
usa 777,888 201,202
—————————————————
please how to solve this problem.
hai
Could any one help me how to get rid of my problem
My problem is:
table name: countryID
accid localaccid names
———————————————–
101 222 india
102 333 india
103 444 india
201 777 usa
202 888 usa
————————————————
I need like this
names localaccid accid
————————————————–
india 222,333,444 101,102,103
usa 777,888 201,202
—————————————————
please how to solve this problem.
It’s easy way.. just follow my instruction.
Step 1:
Create Function..
CREATE FUNCTION [dbo].[udf_UserLocation]
(@id int)
RETURNS VARCHAR(500)
AS
BEGIN
declare @loctemp varchar(500)
SELECT @loctemp=COALESCE(@loctemp + ‘, ‘, ”) + CAST(l.LocName as varchar)
FROM Location where CountryID=@id
RETURN (@loctemp)
END
Then
Run Query..
select CountryName,dbo.udf_UserLocation(id) as ‘Location’ from [Country]
You will get output as u want without using cursor.
–dharmik chotaliya
Hi Pinal,
I want this in Select distinct query result.
It is not working in case of distinct
I have tried like following and i didn’t get desired result
USE AdventureWorks
GO
DECLARE @listStr VARCHAR(MAX)
SELECT DISTINCT @listStr = COALESCE(@listStr+’,’ ,”) + Name
FROM Production.Product
SELECT @listStr
GO
Plz give some solution
please try below query
use AdventureWorks
Go
Delcare @listStr VARCHAR(MAX)
select @listStr =COALESCE(@listStr+’,’ ,”) + ProductData.Name
from(select distinct Name from Production.Product) as ProductData
SELECT @listStr
Go
Hi Pinal,
I got the solution for Select Distinct
but i don’t know about SQL Server 2005 but it is working in SQL Server 2008
USE AdventureWorks
GO
DECLARE @listStr XML
set @listStr=(SELECT DISTINCT COALESCE( Name,”)+’,’
FROM Production.Product
for XML Path(”))
SELECT SUBSTRING(CAST (@ listStr AS VARCHAR) , 1, LEN(CAST (@ listStr AS VARCHAR))-1)
GO
How to add an and in the end like item1,item2,item3 and item4