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
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
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
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!
I have a table with FirstName & Last Name as 2 fields, Help me to get all the full name i.e First Name & Last name’s in a single string.
table1
——
FName LName
—— ——–
S Thakur
P Patel
R Singh
Result should be : S Thakur, P Patel, R Singh
Please help me in this
Hi Priyanka
Try something like this (it’s very similar to the code in the original code post)
declare @FullName varchar(Max)
select
@FullName = coalesce(@FullName + ‘, ‘,”) + FName + ‘ ‘ + LName
from table1
The only difference is you are now coalescing on a concatenated string of the FName and LName
Hope this helps
I tried using COALESCE on SQL Server 2008, but it was giving very inconsistent results. When running as a query, the list would look fine, but if I put the same code into a trigger, there would always be a comma prepended to the list.
I ended up replacing the COALESCE with an equivalent CASE statement which solved the issue:
@S = (CASE WHEN @S IS NULL THEN ” ELSE @S + ‘,’ END) + @ColValue
Hi Pinal…
your articles are very useful…I am an sql server database beginner.
I read that “we can change environment variables using sp’s ,but can’t with udf’s ” from one of your article -stored procedure vs udf.
My doubt is:
How to set environment variables using stored procedures.i did not know how to get those env var values ti t-sql.So,please give me an example to do this.It may be useful for beginners like me..
Thanks,
durga
Hi Pinal…
your articles are very useful…I am an sql server database beginner.
I read that “we can change environment variables using sp’s ,but can’t with udf’s ” from one of your article -stored procedure vs udf.
My doubt is:
How to set environment variables using stored procedures.i did not know how to get those env var values ti t-sql.So,please give me an example to do this.It may be useful for beginners like me..
Thanks,
durga
I am having same problem which Steve has mentioned above. Is anyone know the solution of this problem ?