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
Grouping But With Keeping All Non-Null Values
I Want to Give Serial No in My Query According to Group
How to Exclude Specific Row from Query in MySQL
How to Find the Row Count for All Your Tables in Postgres
How to Retrieve Microseconds or Milliseconds from MySQL Current Time
Regex Pattern Inside SQL Replace Function
Sql71501 - How to Get Rid of This Error
Phpmyadmin Says No Privilege to Create Database, Despite Logged in as Root User
Mysql Workbench Edit Table Data Is Read Only
Ssis Date in Derived Column as Yyyy-Mm-Dd Format
Sql Select Last N Rows, Sort Them Reversed
Sql String: Counting Words Inside a String
Combining Multiple Rows in SQL Server into One
How to Change Date Format in .Net Rdlc Report
Convert a 12 Hour Format to 24 Hour Format in SQL Server
Loop Through Table Rows and Call Stored Procedure on Every Row
What Is the Best Datatype for Storing Urls in a MySQL Database