SQL SERVER – Group by Rows and Columns using XML PATH – Efficient Concating Trick

I hardly get hard time to come up with the title of the blog post. This was one of the blog post even though simple, I believe I have not come up with appropriate title: Group by Rows and Columns using XML PATH – Efficient Concatenating Trick. Anyway, here is the question I received.

“I have a table of students and the courses they are enrolled with the name of the professor besides it. I would like to group the result with course and instructor name.

For example, here is my table:

SQL SERVER - Group by Rows and Columns using XML PATH - Efficient Concating Trick xmlpath1

How can I generate result as following?

SQL SERVER - Group by Rows and Columns using XML PATH - Efficient Concating Trick xmlpath2

Now you can see how easy the question is but so hard to come up with either solution or the title of this blog post. We can use XML PATH and come up with the solution where we combine two or more columns together and display desired result.

Here is the quick script which does the task, ask, I have used temporary tables so you can just take this script and quickly run on your machine and see how it returns results.

Let me know if there are any better ways to do the same.

-- Create table
CREATE TABLE #TestTable (StudentName VARCHAR(100), Course VARCHAR(100), 
Instructor VARCHAR(100), RoomNo VARCHAR(100))
GO
-- Populate table
INSERT INTO #TestTable (StudentName, Course, Instructor, RoomNo)
SELECT 'Mark', 'Algebra', 'Dr. James', '101'
UNION ALL
SELECT 'Mark', 'Maths', 'Dr. Jones', '201'
UNION ALL
SELECT 'Joe', 'Algebra', 'Dr. James', '101'
UNION ALL
SELECT 'Joe', 'Science', 'Dr. Ross', '301'
UNION ALL
SELECT 'Joe', 'Geography', 'Dr. Lisa', '401'
UNION ALL
SELECT 'Jenny', 'Algebra', 'Dr. James', '101'
GO
-- Check orginal data
SELECT *
FROM #TestTable
GO
-- Group by Data using column and XML PATH
SELECT
StudentName,
STUFF((
SELECT ', ' + Course + ' by ' + CAST(Instructor AS VARCHAR(MAX)) + 
' in Room No ' + CAST(RoomNo AS VARCHAR(MAX))
FROM #TestTable
WHERE (StudentName = StudentCourses.StudentName)
FOR XML PATH (''))
,1,2,'') AS NameValues
FROM #TestTable StudentCourses
GROUP BY StudentName
GO
-- Clean up
DROP TABLE #TestTable
GO

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Scripts, SQL Server, SQL String, SQL XML
Previous Post
SQLAuthority Contests – Get 50 Amazon Gift Cards – Experience NuoDB Starlings 1.0.2
Next Post
SQL SERVER – Weekly Series – Memory Lane – #023

Related Posts

24 Comments. Leave new

  • This is not working when applied on Views. Do you know why?

    Reply
  • Hi pinaldave,
    I have Data in table like:
    col1 Col2 Col3 Col4 Col5
    1 2 3 4 3/8/2013
    0 4 2 5 3/16/2013
    1 2 3 0 3/8/2013
    1 2 3 0 3/16/2013
    Need query which output will be like:
    Col 6 3/8/2013 3/16/2013
    Col1 Sum(col1 on above date) =2 Sum(col1 on above date)=1
    col2 Sum(col2 on above date) = 4 Sum(col2 on above date)=6
    col3 Sum(col3 on above date) = 6 Sum(col3 on above date)=5
    col4 Sum(col4 on above date) = 4 Sum(col4 on above date) = 5

    Reply
    • sunil Kumar samal
      August 30, 2013 2:44 pm

      with temp(col6,col7,col8)
      as
      (
      select ‘col1’ as ‘col6′,case when col5=’3/8/2013’ then sum(col1) else ” end as ‘3/8/2013′,
      case when col5 =’3/16/2013’ then sum(col1) else ” end as ‘3/16/2013’
      from #TestTable1
      group by col5
      union
      select ‘col2’ as ‘col6′,case when col5=’3/8/2013’ then sum(col2) else ” end as ‘3/8/2013′,
      case when col5 =’3/16/2013’ then sum(col2) else ” end as ‘3/16/2013’
      from #TestTable1
      group by col5
      union
      select ‘col3’ as ‘col6′,case when col5=’3/8/2013’ then sum(col3) else ” end as ‘3/8/2013′,
      case when col5 =’3/16/2013’ then sum(col3) else ” end as ‘3/16/2013’
      from #TestTable1
      group by col5
      union
      select ‘col4’ as ‘col6′,case when col5=’3/8/2013’ then sum(col4) else ” end as ‘3/8/2013′,
      case when col5 =’3/16/2013’ then sum(col4) else ” end as ‘3/16/2013’
      from #TestTable1
      group by col5

      )
      select col6,SUM(col7) as ‘3/8/2013’,SUM(col8) as ‘3/16/2013’ from temp
      group by col6

      Reply
  • Good stuff! Thank you!

    Reply
  • Madina Malikova
    October 23, 2013 4:46 pm

    Thanks a lot!

    Reply
  • Wow. Thanks.
    Is there a way to put the Stuff-part in a function?

    Reply
  • Can anyone please explain me the meaning of – FOR XML PATH (”))
    ,1,2,”) ???

    Reply
    • For xml path is used to return tables data in xml formate and at the last 1,2 is used with STUFF ( character_expression , start , length , replaceWith_expression )

      Reply
  • Mohamed Kalithu
    April 2, 2014 6:23 pm

    I need to collect all the parentmenuid from child id. For example below is my table
    ID MenuCaption ParentMenuID

    1 Home NULL

    2 ClientAdmin 1

    3 Mastersetup 2

    4 Page1 3

    5 Setting 4

    I expecting result set as below when i give child’s parent id (4)

    ID MenuCaption ParentMenuID
    1 Home NULL

    2 ClientAdmin 1

    3 Mastersetup 2

    4 Page1 3

    Reply
  • Great post. How can I reverse the order in which the values are concatenated?

    Reply
  • I have did it..

    SELECT *
    FROM (
    SELECT
    QID,[Option],
    Total
    FROM
    (
    select QID, case OPID
    when 1 then ‘OP1’
    when 2 then ‘OP2’
    when 3 then ‘OP3’
    when 4 then ‘OP4’
    when 5 then ‘OP5’
    END as [Option] ,Count(UID) as Total from UserResponse group by QID,OPID
    )as T
    ) as s
    PIVOT
    (
    SUM(Total)
    FOR [Option] IN(OP1,OP2,OP3,OP4,OP5)
    )AS PivotTable

    Reply
  • Just awesome I was trying to create a JSON for child parent so I wanted region–>territories this STUFF worked like charm great

    Reply
  • not working for sql server 2008. any alternative????

    Reply
  • It works perfectly for me on SQL Server 2008. As always, thank you for your article

    Reply
  • Thank you

    Reply
  • Jagdeep Mankotia
    March 8, 2017 12:14 pm

    Great! Its working fine in SQL2012, I tried few more method to achieve your requirement, but dint able to find the better solution.

    Thanks for your article.

    Reply
  • This does not work as the lists get truncated at about 256 characters

    Reply
  • Thank you so much sir

    Reply

Leave a Reply