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
Why are you separating rows into columns? A csv should have every row as a new row and columns separated by commas. You just hvae one row with each data row separated by commas. Horrible example.
I used the following query to obtain this result
SELECT
STUFF((SELECT ‘,’ + NumberCols FROM NumberTable
FOR XML PATH(”)), 1, 2, ”)
Select stuff(
(Select ‘,’+Name from Fruits
for Xml Path(”)),1,1,”)
If we place 2 Masking will be appied for first two letters of the String.Thank you very Much for your Answer
if it is integer/decimal field your query is returning accumulated value rather than comma separated value, could you please tell me the fix for integer/decimal value
Thanks, i managed to do
DECLARE @listStr VARCHAR(MAX)
SET @listStr = ”
SELECT @listStr = CAST(Paidamount AS VARCHAR) + ‘, ‘ + @listStr
FROM StudentDetails INNER JOIN
Payment ON StudentDetails.Id = Payment.StudentId INNER JOIN
PaymentCenter ON Payment.PaymentCenter = PaymentCenter.Id
WHERE StudentDetails.Id = Payment.StudentId
AND Payment.IsPaid 1 AND Payment.PaidYear = 2013 AND
(StudentDetails.PaymentCenter = 1)
SELECT @listStr
I have a doubt regarding this. Is there anything special with ‘COALESCE’ here.?
Cant we produce the same Output with ‘ISNULL’ using the below code.
—Script—
SELECT @ListStr = ISNULL(@ListStr + ‘,’,’Colours : ‘) + Colour
FROM
(
SELECT ‘Red’ AS Colour
UNION ALL
SELECT ‘White’
UNION ALL
SELECT ‘Blue’
UNION ALL
SELECT ‘Black’
)M
–// Output
SELECT @ListStr
————————————————–
So why should we go for COALESCE.
Thanks in advance.. :)
Thanks a lot dear….
select *, STUFF(
(SELECT ‘, ‘ + cast(UGL.GROUPID as varchar(10))
FROM USERINFO UI
where UI.ID=UGL.USERID and COMPANY=’etis’
FOR XML PATH (”)) , 1, 1, ”) AS [USER_GroupS]
from USERGROUPLIST UGL
What is NumberCols ? How can we dynamically create the column list without knowing the number of columns in the table?
It is the column of number datatypes. To generate column list, refer this
how can i achieve same when i have output from 2 columns.
so instead of NumberCols i have NumberCols1 + NumberCols2 as NumberCols
In the first scenario, the result set will return ‘NULL’, if column NumberCols will have any NULL value.
We can use the below query :
declare @str varchar(max)
select @str=coalesce(@str+’,’,”)+NumberCols from NumberTable
where NumberCols is not null
select @str
Please correct me if I am wrong.
You are correct. NULLs have be to handled.
Thanks for this!
// Create coloumn with seprated column value.from row value
SELECT DISTINCT p.[Account ID],p.[Contact ID],p.[Account Name],p.[Email],p.[Contact Role],
STUFF((SELECT distinct ‘,’ + p1.Role
FROM finalsheet p1
WHERE p.[Contact ID]= p1.[Contact ID]
FOR XML PATH(”), TYPE
).value(‘.’, ‘NVARCHAR(MAX)’)
,1,1,”) RoleS
FROM finalsheet p
–group by p.[Account ID],p.[Contact ID],p.[Account Name],p.[Email],p.[Contact Role]
// How about by using FOR XML PATH?
declare @Result nvarchar(max)
set @Result = (select [NumberCols] + ‘, ‘ from NumberTable FOR XML PATH(”))
select substring(@Result,0,len(@Result)-1) as Result
How do you create a coma delimited string from a row of multiple columns? meaning if I have [row] 1 | 2 | 3 | 4 and I want it to display 1,2,3,4 which I can set to a string variable.
How to get each column value separated by comma and create multiple rows for new combination of the column value in SQL?
I’m trying to split the value in comma separated column in SQL table and re-combine using below logic to create additional rows for the same id.
for example: lets say I have a table call table_A
id value
1 a,b,c,d
2 a,b,c
3 a,b
I know how to split these values into multiple rows with a single value but now i need them to be displayed like below.
final output should be look like this:
id value
1 a,b
1 b,c
1 c,d
2 a,b
2 b,c
3 a,b
Any idea would be appreciated!
CREATE TABLE Testdata
(
SomeID INT,
OtherID INT,
String VARCHAR(MAX)
)
INSERT Testdata SELECT 1, 9, ‘18,20,22’
INSERT Testdata SELECT 2, 8, ‘17,19’
INSERT Testdata SELECT 3, 7, ‘13,19,20’
INSERT Testdata SELECT 4, 6, ”
INSERT Testdata SELECT 9, 11, ‘1,2,3,4’
; with c as
(
SELECT A.SomeID,
sa.a.value(‘.’, ‘VARCHAR(10)’) AS Data
,ro=row_number() over (partition by A.SomeID order by sa.a.value(‘.’, ‘VARCHAR(10)’))
FROM
(
SELECT SomeID,
CAST (” + REPLACE(String, ‘,’, ”) + ” AS XML) AS Data
FROM Testdata
) AS A CROSS APPLY Data.nodes (‘M’) AS sa(a)
)
,
ccc as (
select c.* ,strcon = stuff((select ‘,’ + c1.data
from c c1
where c.SomeID = c1.SomeID and c.ro <= (c1.ro) and (c1.ro – c.ro)<=1
for xml path(''),type).value('.','nvarchar(max)'),1,1,'')
,max(ro) over (partition by SomeID ) max_ro
from c
)
select SomeID , strcon
from ccc
where ( max_ro ro or max_ro =1)
;
Note: XML_PATH will fail if your data has xml-sensitive data. for example ”