Sql Server - Join Rows into Comma Separated List

SQL Server - join rows into comma separated list

You are missing the condition inside the sub query.

SELECT t2.Id, STUFF((SELECT ',' + CAST(VALUE AS varchar) FROM @MyTable t1  where t1.Id =t2.ID FOR XML PATH('')), 1 ,1, '') AS ValueList
FROM @MyTable t2
GROUP BY t2.Id

Demo

How can I combine multiple rows into a comma-delimited list in SQL Server 2005?

DECLARE @XYList varchar(MAX)
SET @XYList = ''

SELECT @XYList = @XYList + CONVERT(varchar, X) + ',' + CONVERT(varchar, Y) + ','
FROM POINTS

-- Remove last comma
SELECT LEFT(@XYList, LEN(@XYList) - 1)

How to take multiple rows and make a comma separated list in SQL

Please tag your database for more info.

This is one way for Oracle where Ihave used a concatenate sign which is || to concatenate(put two strings together in one) and between them I have also concatenated a comma ,. You can also see that I have used double quotes for the column named desc. I did it because it is not a good practice to call your columns with keywords and word desc is used for example when you order by some column(at the end of the query) you can order by that column ascending then you use asc or descending when you can use desc. Also in both examples I used keyword as to give a name to this concatenated column.

SELECT class_id, cpab.ability_id || ',' || 
ab.ability_id || ',' ||
ab.name || ',' ||
class_id || ',' ||
cpab.name || ',' ||
hit_die || ',' ||
"desc" || ',' ||
isPlayable as values
FROM rpg.class_primary_abilities AS cpab
INNER JOIN rpg.abilities AS ab ON cpab.ability_id = ab.ability_id
INNER JOIN rpg.classes AS cl ON cpab.class_id = cl.class_id;

This is another for MYSQL where I have used concat to concatenate column values and I have used different single quotes for desc column.:

SELECT class_id, concat(cpab.ability_id, ',' , 
ab.ability_id, ',' ,
ab.name, ',' ,
class_id, ',' ,
cpab.name, ',' ,
hit_die, ',' ,
`desc`, ',' ,
isPlayable) as values
FROM rpg.class_primary_abilities AS cpab
INNER JOIN rpg.abilities AS ab ON cpab.ability_id = ab.ability_id
INNER JOIN rpg.classes AS cl ON cpab.class_id = cl.class_id;

In both examples you have columns with same name from different tables and theer you will have to use aliases when calling them in your select clause like I have did in my example: cpab.ability_id and ab.ability_id but please note that I do not know if they are from cpab and ab tables.

SQL Server join results as comma separated list

You can use something like this in SQL Server

select co.CompanyID, AllIndustries = 
(select (cast(industry as varchar(200))+',') as [text()]
FROM company c,
company_industry_map m
WHERE c.company_id = m.company_id and c.company_id = co.company_id
order by industry_id for XML PATH(''))
from Companies co


Related Topics



Leave a reply



Submit