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 (http://www.SQLAuthority.com)
pinaldave
Hi Pinal,
U have used COALESCE( colname + ‘,’,”)
What is the meaning of Second parameter here.????
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
[...] 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 [...]
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?