Skip to content
Home ยป How to get multiple rows data in single row in SQL

How to get multiple rows data in single row in SQL

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..

SQL Server Interview Q & A

 

 15,019 total views,  9 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.