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 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
Reference : Pinal Dave (http://blog.SQLAuthority.com)
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
Shashi Kant chauhan,
COALESCE is required for non null values. If there is NULL Value in your string you will have your resultset as NULL.
Using COALESCE is good idea.
Regards,
Pinal Dave
( http://www.SQLAuthority.com )
HI
i want to separate the values given in a single row separeted by comma into multiple row..
for eg
Input : ab,bc,cd,de
output:
ab
bc
cd
de
hi
did u get this query.
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?
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!
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
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%’
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.
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
I have two tables (please note that column headers are dynamic – not necessarily 3 or 4 columns..but the same numbr of records will be present in the column data):
Table 1:
SortSeqID ColumnHeader
1 ‘ColA,ColB,ColC’
2 ‘ColA,ColB,ColC,ColD’
3 ‘ColA,ColC,ColD’
Table 2:
SortSeqID ColumnData
1 ‘1,2,3’
2 ’11,13,243,5’
3 ‘1,5,6’
Need output like:
SortSeqID ColA ColB ColC ColD
1 1 2 3
2 11 13 243 5
3 1 5 6
Refer this
http://beyondrelational.com/blogs/madhivanan/archive/2008/09/11/splitting-delimited-data-to-columns-set-based-approach.aspx
Hi Pinal,
Can I use COALESCE in update statement ?
when I am trying to update the rows to temp table the concatenation doesn’t happen.
It picks only the first value
Please help
Create Table #TempData
(
Pe_Logn Varchar(10), Team Varchar(8000)
)
Insert Into #TempData
Select Distinct(PE_DEPT), NULL from iP_Person
/* Select *from #TempDate */
Pe_Logn Team
Finance NULL
HR NULL
IT NULL
/* Getting the people for each department */
Update #TempData
Set Team = COALESCE(IsNull(TEAM, ”) + ‘,’,”) + Pe.Pe_Logn from iP_Person Pe
Where Pe.Pe_Dept = #TempData.Pe_Logn
/* Select *from #TempData */
Pe_Logn Team
Finance ,ABC
HR ,DEF
IT ,XYZ
Actual output should have been
Pe_Logn Team
Finance ,ABC, 123, !@#
HR ,DEF, 234, @#$
IT ,XYZ, 345, #$%
Regards,
Uday
Hi!
I have a query like this:
InvoiceNum Depart.
————— ————–
11000 400
11000 404
15000 572
And I need:
InvoiceNum Depart.
————— ————–
11000 400, 404
15000 572
Is it possible? Thanks!!!!
Search for rowset concatenation in this site
If I want this view would COALESCE work? How else can I get this please?
Data – All in one table
Col1 Col2
—— ———-
USA Denver
USA Seattle
USA Chicago
Canada Vancouver
Canada Calgary
Result –
Col1 Col2
—– ———–
USA Denver, Seattle, Chicago
Canada Vancouver, Calgary
Pinal,
How can I use this comma separated list in a query? like where value IN ( Select commaSeparatedList from MyTable)
This script doesn’t work if there are any null values in the set of rows being composited.
Try this instead:
DECLARE @List VARCHAR(5000)
SELECT @List = COALESCE(@List + ‘, ‘ + MyField, MyField)
FROM MyTable
select @List as MyDisplayName
(from http://www.mitchelsellers.com/blogs/articletype/articleview/articleid/289/creating-comma-separated-list-in-sql.aspx)
Only slightly different but more robust
Hi Pinal,
I have taken one variable named @Period_List As varchar(max). I have set its vale as
@Period_List = ‘column1 int, column2 int, column 3′
Now i am trying to create table using this variable,
Like,
CREATE Table #TempTable (@Period_List)
But it gives error. can you please tell me another way or solution for this problem.
I want help with sql statment. when I select the data I get is like this
1 2232 eq233 1000 12-12-2011 11:11 39 9.2 text text
I want this output to have comma instead of space like
1, 2232, eq233, 1000, 12-12-2011 11:11, 39, 9.2, text text,
can some please help.
In the toolbar Goto Query–>Query options–>Results–Text . Select output format as comma delimited
Now set the result mode to text and run the query
Can someone please help me? I have a column of string data collected from a survey. e.g. (Yes; No; Maybe; Don’t know), assigned to a number of respondents. I want to create a list in a seperate COLUMN (retaining a Response heading) for each respondent. i.e.
Respondent Response
A Yes
A No
A Yes
B Maybe
B No
B Yes
Hi pinaldave,
The code is help me a lot in one of my project.
I really appreciate.
Thanks & Regards,
Prashant Kauthekar
Hi Pinal Dave,
I have referenced this code many times. I appreciate you maintaining this site. Nicely done!
-Steve
hi
i want to separate the values given in a single row separeted by comma into multiple row..
for eg
Input : ab,bc,cd,de
output:
ab
bc
cd
de
@Mazharkhan
Did you find your answer..i have same problem..please revert
HOW TO USE SELECT STATEMENTS INSTEAD OF ARGUMENTS IN SPLIT COMMA FUNCTION incase of ms sql 2008 server?it returns value for the below query
select LISTMEMBER from SysAdmin.fnSplitComma (’2,3,5′)
like LISTMEMBER
2
3
5
but how to get the value when we specifies a column in table which has data in the format 2,3,4 in first row,2,5 in second row.BELOW select statement wont work for that.
SELECT LISTMEMBER FROM SysAdmin.fnSplitComma(SELECT IMPLanguages FROM CFI.InstructionalMediaPackage WHERE IMPLanguages=p.pkLanguageID) AS
‘nameList’
FROM SysAdmin.M_Lang AS p
perfect!