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)
pinaldave
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 )
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.
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
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?
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