Following script will create common separate values (CSV) or common separate list from tables. convert list to table. Following script is written for SQL SERVER 2005. It will also work well with very big TEXT field. If you want to use this on SQL SERVER 2000 replace VARCHAR(MAX) with VARCHAR(8000) or any other varchar limit. It will work with INT as well as VARCHAR.
There are three ways to do this. 1) Using COALESCE 2) Using SELECT Smartly 3) Using CURSOR.
The table is example is:
TableName: NumberTable
| NumberCols |
| first |
| second |
| third |
| fourth |
| fifth |
Output : first,second,third,fourth,fifth
Option 1: This is the smartest way.
DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' , '') + NumberCols
FROM NumberTable
SELECT @listStr
Please make a note that COALESCE returns the first NOT NULL value from the argument list we pass.
Option 2: This is the smart but not the best way; though I have seen similar code many times.
DECLARE @listStr VARCHAR(MAX)
SET @listStr = ''
SELECT @listStr = @listStr + NumberCols + ','
FROM NumberTable
SELECT SUBSTRING(@listStr , 1, LEN(@listStr)-1)
I sometime use ISNULL(NumberCols,’NullValue’) to convert NULL values to other desired value.
Option 3: Cursor are not the best way, please use either of above options.
Above script can be converted to User Defined Function (UDF) or Storped Procedure (SP).
Reference : Pinal Dave (https://blog.sqlauthority.com)
46 Comments. Leave new
Hi Pinal,
U have used COALESCE( colname + ‘,’,”)
What is the meaning of Second parameter here.????
It means that If the value of the first expression is null, make it empty
i tried using both ways 1st and 2nd
when i use these techniques on large set of data ,say one thousand records i get a timed out sql exception.this works fine on small amount of data (few rows).
how could i achieve this on large data………………
Hi Pinal,
I have a column with dx codes separated by commas. I need to break these out into separate columns within the same table. How is this done or what is the best way to get this done?
Here is an example:
DX
————
V72.0, V32.0, 245.
OUTPUT NEEDED:
DX DX1 DX2 DX3
——– ——- —— ——-
V72.0, V32.0, 245. V72.0 V32.0 245
Thanks!
Mina
Hi,
I used COALESCE in my project to reterive the comma separate values.
syntax :-
COALESCE (storage, separetor, startvalue)
storage :- variable which contain final output. In option 1 @listStr is storage.
separetor :- value which distigush between two item. for example here ‘,’ (comma) you can put any value like ‘/’,’A’.
startvalue :- list start with this value.
for example
I want Output : start , first,second,third,fourth,fifth
COALESCE(@listStr, ‘,’ , ‘start,’).
thanks
Mahendra
Hi Pinal,
In my company we need to do opposite of above and that is parsing the comma seperated values into rows of table.
I found following script which works very fast as I have parsed 20 million records in just under 5 mins.
Hope this will help others too.
–First Create a table with numeric values form 1 to 8000
CREATE TABLE Tally
(ID INT)
DECLARE @count INT
SELECT @count =1
WHILE @count < 8001
BEGIN
INSERT INTO Tally(ID)
SELECT @count
SELECT @count=@count+1
END
SELECT Comma_Seperated_List,
NULLIF(SubString(‘,’ + product_list + ‘,’ , ID , CharIndex(‘,’ , ‘,’ + product_list + ‘,’ , ID) – ID) , ”) AS ‘Value’
FROM Tally T,
My_table M
WHERE ID 0 –remove this line to keep NULL rows
Your comments are appriciated.
Thanks,
SK
Thanks you are the best…
Is it possible to get distinct values only?
How to get only distinct rows as comma separated?
The code should be
DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+’,’ , ”) + NumberCols
FROM (SELECT DISTINCT NumberCols from NumberTable) as t
SELECT @listStr
I prefer such:
Select name+’,’
from something
for xml path(”)
The POWER :-) of this solution that you can use this in subquerys also.
Better I make some example:
—————————————————————–
SELECT campaign_name
, ( SELECT member_email +’;’
FROM competition (NOLOCK)
WHERE campaign_key = C.c_key
FOR XML PATH(”)
) recipients
FROM campaigns (NOLOCK) AS C
WHERE DATE > GETDATE()
very good solution for concating string within a select statement
Hi Pinal,
I want the record to be display
ID Name
1 abc, cde, efg
2 111,222,333
3 56
Hi,
please refer:
Let me know if you have any other situations.
Thanks,
Tejas
There is a better Option available for the same job.
select cast(NumberCols as varchar)+’,’ from NumberTable
for xml path (”)
it saves so many operations and rest all query it self speaks.
Hi Pinal,
Thank you ,this article helped me a lot.
You can try this also (Tested in SQL 2005)
SELECT schema_Name(schema_id) AS SchemaName,
name AS TABLE_NAME,
ColumnsCount ,
STUFF(COLUMN_NAME, 1, 1, ”) AS COLUMN_NAME
FROM sys.tables t
CROSS APPLY
( SELECT ‘,’ + name AS [text()]
FROM sys.columns c
WHERE c.object_id = t.object_id FOR XML PATH(”)
) o (COLUMN_NAME)
CROSS APPLY
( SELECT COUNT(*) ColumnsCount
FROM sys.columns c
WHERE c.object_id = t.object_id
) CC
— Where t.name not in (”)
ORDER BY 1, 2, 3
Table :
ID NAME City
1 ABC MCLEAN
2 ABC RESTON
3 BCD ASHBURN
4 BCD HERNDON
5 DEF CHICAGO
——————————————
Declare @Name varchar(MAX)
Select @Name = COALESCE(@Name+’,’,”)+ ”” + Name + ”” FROM (Select Distinct NAME from Testtable) as t
Select @Name
Declare @Front Varchar(5000)
Select @Front = ‘(‘ + @Name + ‘)’
Select @Front
Select * from Testtable
Where (NAME in (@Front))
I want to see above query works, I want the @Front to work, as I have to use Variable to pass.
Appreciate any reply back .
Thanks
Venkat
EXEC(‘Select * from Testtable Where (NAME in (‘+@Front+’))’
This is awesome.
Thank you!
Hi Pinal,
We can get comma separate values using single query.. see below example,
SELECT
STUFF
(( SELECT ‘, ‘ + NumberCols
FROM
NumberTable
WHERE
NumberCols is not NULL
ORDER BY
NumberCols
FOR XML
PATH (”), TYPE ).value(‘.’, ‘varchar(max)’), 1, 2, ”) AS commaseperatelist
What you say ? its good for best practice or ??… ?
Thank You!
In case your table list is empty, you will get a error in the o/p
If you use like below u wont get such error…
DECLARE @listStr VARCHAR(MAX)
SET @listStr = ”
SELECT @listStr = @listStr + NumberCols + ‘,’
FROM NumberTable
if(@listStr ”)
SELECT SUBSTRING(@listStr , 1, LEN(@listStr)-1)
Print @listStr
I would like to use the resulting list as parameters to a stored procedure; will I have to parse the list to add quotation marks around the string values?
Yes. But if you use version 2008, read about Table-valued-parameter