Skip to main content

SQL SERVER – Creating Comma Separate Values List from Table – UDF – SP

 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.

Comments

Post a Comment

Popular posts from this blog

Select Names from table which have vowels

  Problem Query the list of  CITY  names from  table  which have vowels (i.e.,  a ,  e ,  i ,  o , and  u ) as both their first  and  last characters. Your result cannot contain duplicates. Input Format The  STATION  table is described as follows: Field Type ID NUMBER CITY VARCHAR2(21) STATE VARCHAR2(2) LAT_N NUMBER LONG_W NUMBER STATION where  LAT_N  is the northern latitude and  LONG_W  is the western longitude. MYSQL select distinct city from station where (city like 'a%' or city like 'e%' or city like 'i%' or city like 'o%' or city like 'u%' ) and ( city like '%a' or city like '%e' or city like '%i' or city like '%o' or city like '%u' )
1 comment
Read more

SQL SERVER – Fix : Error 1702 CREATE TABLE failed because column in table exceeds the maximum of columns

  Error 1702 CREATE TABLE failed because column in table exceeds the maximum of columns SQL Server 2000 supports table with maximum 1024 columns. This errors happens when we try to create table with 1024 columns or try to add columns to table which exceeds more than 1024. Fix/Solution/WorkAround: Reduce the number of columns in the table to 1,024 or less
Post a Comment
Read more
Q- What will be the result of the query below? Explain your answer and provide a version that behaves correctly. select case when null = null then 'Yup' else 'Nope' end as Result; Ans-This query will actually yield “Nope”, seeming to imply that  null  is not equal to itself! The reason for this is that the proper way to compare a value to  null  in SQL is with the  is  operator, not with  = . Accordingly, the correct version of the above query that yields the expected result (i.e., “Yup”) would be as follows: select case when null is null then 'Yup' else 'Nope' end as Result;
Post a Comment
Read more