This article demonstrates how to Convert multiple rows into a single comma separated row, it can be done by using in-built SQL functions.
As you can see in below screenshot, here we have an input data where each categories are having multiple subcategories associated with it, and our requirement is to convert those subcategory into a single comma separated row.
Lets create a two sample tables category and subcategory and insert some sample data in tables as shown below.
create table Category (CatId Int, CategoryName Varchar(50) ) insert into Category (CatId, CategoryName) values (1, 'Phone'), (2, 'Furniture'), (3, 'Laptop') create table SubCategory (SubCatId int, SubcategoryName Varchar(50), CatId int) insert into SubCategory (SubCatId, SubCategoryName, CatId) values (1, 'Samsung Glaxy', 1), (2, 'Iphone 5S', 1), (3, 'IPhone 7',1), (4, 'Sofa Set', 2), (5, 'Dinning Table', 2), (6, 'Round Table', 2), (7, 'Chairs',2), (8, 'HP Elitebook', 3), (9, 'Samsung Glaxy Book Go', 3), (10, 'Apple MacBook',3)
Now table has been created as you can see below.
select * from subcategory
select * from subcategory
Lets see different ways of converting rows into single comma separated rows.
1. Using STRING_AGG function
STRING_AGG function concatenates values for any given string expressions and separate the string by specified separator.
Lets write the below T-SQL query:
select c.CategoryName, STRING_AGG(s.SubCategoryName, ', ') as SubCategoryName from category c inner join subcategory s on c.CatId=s.CatId group by CategoryName
You can see the query output.
2. Using STUFF and XML function
You can use STUFF and XML PATH functions to covert multiple rows into single comma separator rows.
SQL STUFF function is used to delete a sequence of given length of characters from a string and inserting the given sequence of characters from the specified starting index.
XML PATH adds the element tag to XML result set.
SELECT
c.CategoryName,
STUFF((SELECT ', ' + s.SubCategoryName
FROM subcategory s
WHERE c.CatId=s.CatId
FOR XML PATH('')), 1, 1, '') as SubCategories
FROM category c
GROUP BY c.CategoryName, c.CatId
ORDER BY c.CatId
Also Read..
