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..
15,019 total views, 9 views today