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