Roll Up Multiple Rows into a single rows and column.

Sometimes we got a requirement where we have to bind the multiple rows into a single row and column. For example the requirement is like, we have a table called tblCategory (parent table) and another table tblBrands (child table) which stored the data as following:-

tblCategory                                    

tblBrands

                                                         

And we want the result display as:

                                             

Now we have to find out the best way to achieve this requirement. 

The solutions which we are going to explore will use two SQL commands STUFF and FOR XML. We will explain about these commands later in this tutorial.

As all of us will be aware about INNER JOIN so first lets write it in very simple manner:-

SELECT   c.cat_name

,b.brand_nme

FROM tblCategory c

JOIN tblBrands b ON b.cat_id = c.id

ORDER BY 1, 2

And we will get the results in following way

Lets take one step ahead and use FOR XML PATH option which will return the result as XML string and will put all the data into one row and column. 

SELECT   c.cat_name

,b.brand_nme

FROM tblCategory c

JOIN tblBrands b ON b.cat_id = c.id

ORDER BY 1, 2

FOR XML PATH ('')

And here the result will appear as 

Now try to convert the join into part of the select statement

SELECT  c.cat_name

,(SELECT '; '+b.brand_nme FROM tblBrands b WHERE b.cat_id = c.id FOR XML PATH('')) [Section]

FROM tblCategory c

ORDER BY 1

The result would be like

Now finally use the STUFF command to fulfill our requirement

SELECT  c.cat_name

,STUFF((SELECT '; '+b.brand_nme FROM tblBrands b WHERE b.cat_id = c.id FOR XML PATH('')), 1, 1, '') [Section]

FROM tblCategory c

GROUP BY c.cat_name, c.id

ORDER BY 1

And here we go!

There might be other best options to achieve this requirement but this one I used during my project. Other best solutions and comments are also most welcome!