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 all,
Wanted to know if we can get comma separated list for 1 column and all get rest columns w/o commas in a query having multiple joins?
Thanks,
hello Pinal,
I have a requirement where a variable with comma separated value is passed as a parameter to a SP and those comma separated values are the field names.Here I want to display only the columns mentioned in the input paramenter:
Ex:-
Table1 is a table with following columns
Col1
Col2
Col3
Col4
Col5
Col6
Col7
Col8
@Param=’Col1,Col2,Col3,Col5,Col8′ — this is the parameter that wl be input to SP.
and I want the above mentioned columns to be displayed when the SP is executed.
Thanks in Advance
how can i display idcount in message box ? i am using Linq to sql and this is my stored procedure.. please replyy……
ALTER PROCEDURE [dbo].[Transaction]
(
@Patient nvarchar(50),
@E_TO nvarchar(50),
@R_type int,
@User_id uniqueidentifier,
@ReportType nvarchar(50),
@Patient_no int,
@Patient_ID_NO numeric(10,0),
@idcount numeric(18,0)=NULL output
)
AS
BEGIN
declare @tempid numeric(18,0)
set @tempid = 0;
declare @idcnt numeric(18,0)
select @idcnt =isnull( max(idcount),0) from Transactions where year(R_date)=year(getdate())
if (@idcnt =0)
set @tempid=1
else
set @tempid = @idcnt +1
INSERT INTO dbo.Transactions
(
Patient,E_TO,R_date,R_from,[User_id],
report_type,Patient_no,Patient_ID_NO,idcount
)
values
(
@Patient,@E_TO,getdate(),@R_type,@User_id,@ReportType,@Patient_no,@Patient_ID_NO,@tempid
)
End
Any Suggestions for how I could do this grouping by a header ID. Example: i have 1 invoice with 5 services, I want 1 row with 2 columns, Invoice and list of services. Here is how i am currently pulling this off using a subquery on the table i am selecting from, but i want to make it perform better:
Stuff(
(select ‘, ‘ + ServiceTypeWithNotes
From WisCTE c
where c.WorkItemID = cte.WorkItemID
FOR XML Path(”)
),1,1,”) as ServiceList
SELECT *FROM A
SELECT *FROM C
SELECT *FROM A
WHERE NO1 NOT IN
(
SELECT NO1 FROM C
)
CREATE FUNCTION ITSV2(@A VARCHAR(10))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @B VARCHAR(10)
SET @B=”
SELECT @B=NO1+’,’+@B FROM A
WHERE NO1 NOT IN
(SELECT
NO1
FROM
C
)
RETURN SUBSTRING(@B
END
SELECT dbo.ITSV2(”)
That only returns one column. I am looking for 2, first column being the header rowID the second being list of services for header rowID.
I have a table with
Header rowID Service
1. A
1. B
1. C
I’m looking for
1. A,B,C
With out doing a sub query joing on itself
Select
RowID
,(
Select stuff(CommaDelimintedserviceList)
From table1 b
Where b.rowID = a.rowID
)
From table1 as a
Or is a best way?
Yes thats a good way Otherwise do it inf the front end application
NICE EX.
This is awesome along with everything else from Pinal Dave.
Thanks
Ned
hi pinal;
i want to store the select clause values into string.if i use one column able to store into string,but more than one column not able to store,please give any suggestion.
If you want to store more than one column values, how do you want to be added a comma seperated?
I want to pass my result of in my select query ?? like this
DECLARE @MaxTRxID as int
DECLARE @Str as varchar(MAX)
DECLARE @Str1 varchar(MAX)
SET @MaxTRxID = 1700
SELECT @Str = COALESCE(@Str + ‘,’, ”) + ReferenceNo FROM [iC.SI.WC.PosLog.TrxInfo]
WHERE TrxId > @MaxTRxID
SET @Str1 = @Str
select * from [iC.SI.WC.PosLog.CashPayment.LineItem] where ReferenceNo in (@Str1)
but its not working can anyone help?
pLEASE SOMEONE HELP ME
Hello Guys,
I would appreciate if anyone could help me.
I am using COALESCE exactly like in the initial example of the post (copied below)
DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+’,’ ,”) + Name
FROM Production.Product
SELECT @listStr
My list is also Varchar(max) but i want to retrieve these values in ascending format using order by?
Can i do this? and if yes could you please give me a simple example using the above script?
Also if i cannot do this do you have any other idea how to achieve the same results?
Thanks in advance!!
This resolved with table variable in a function
DECLARE @TableVar table (list VARCHAR(max) NOT NULL)
INSERT INTO @TableVar ( list )
SELECT Name
FROM Production.Product
ORDER BY Name
DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr + ‘ ; ‘ , ”) + list
from @TableVar
RETURN @listStr
it is possible to insert this code to function, which I able to call in the application code
Now I have somone likie this:
create procedure LIST_p(@COLUMN varchar (max), @TABLE varchar (max) )
AS
DECLARE @SYNTAX VARCHAR(MAX)
SET @SYNTAX = ‘DECLARE @listStr VARCHAR(MAX) select @listStr = COALESCE(@listStr+”,” ,””) + ‘ + @COLUMN + ‘ from ‘+ @TABLE +’; select @listStr;’
execute (@SYNTAX)
go
, but I have a question how to execute this procedure using select query
Hi Pinal,
How to create comma separated list of values by group.
Hi this is satish bandi
I want only in singe row from two rows Like
Sno Name Age
”””””|”””””””’|””””’
1 sa 20
1 sa 30
1 sa 40
Now I want
Sno Name Age
”””””|”””””””’|””””’
1 sa 20,30,40
Hi This IS satish
id name services(I have)
———————————–
1 Joe AA
1 Joe AB
1 Joe AC
2 Judy GH
2 Judy GC
3 Kevin AA
3 Kevin GH
Result Set:
id name services (I want)
———————————–
1 Joe AA, AB, AC
2 Judy GH, GC
3 Kevin AA, GH
How can i get like this
solution to vyshu’s problem
declare @tt varchar(25) = ”
,@ttlen tinyint = 0
select @tt = ‘ab,bc,cd,de’
,@ttlen = LEN(@tt)
create table #tt(name varchar(10))
while(@ttlen > 0)
begin
insert into #tt
select LEFT(ltrim(@tt),2)
select @tt = STUFF(@tt,1,3,”)
set @ttlen = LEN(ltrim(@tt))
end
select * from #tt
same output i wanted using user defined function , i wanted to pass the ids with comma separed to the function and get the description as per the id.
ex: my passing dynamic parameters ids should be like this ids = ‘1,4,5,6’ and from function it should return with comma separated description..like san,ta,sri,ssss.
Thanks
Santosh
Hi Pinal,
there is a task in which user is asking for comma delimited text file. i am running the query in sql server 2008.
Hello Pinal,
I have a problem I have been trying to solve. I have an EDI 834 text file I need to parse into a SQL table using multiple custom delimiters. The file is delimited with a { and member data between different leading identifiers, for example ~DTP{348{D8{20121101~AMT{C1{0 would mean for the month of 2012 10 01 the copay would be $0. The other leading indentifiers would be ~INS – Insured Benefit OR MEMBER LEVEL DETAIL
~REF – Reference Information : The REF segment is required to link the dependent(s) to the subscriber.PRIOR COVERAGE MONTHS
~DTP – Date or Time or Period
~NM1 – Individual or Organizational Name : Either NM1 or N1 will be included depending on whether an individual or organization is being specified. MEMBER EMPLOYER or RESPONSIBLE PERSON
~PER – Administrative Communications Contact
~N3 – Party Location
~N4 – Geographic Location
~DMG – Demographic Information
~LS – Loop Header
EC – Employment Class
~LS – Loop Header
~LX – Transaction Set Line Number
~N1 – Party Identification
~REF – Reference Information
~AMT – Monetary Amount Information
~COB – COORDINATION OF BENEFITS
~LX – Transaction Set Line Number
~N1 – Party Identification
~DTP – Date or Time or Period
~LE – Loop Trailer
~SE – Transaction Set Trailer
Any help of direction would great.
thanks