You are here:   Blog
Register   |  Login

Blog Archive:

* Can be used in order to search for older blogs Entries

Search in blogs

Blog Categories:

* Can be used in order to search for blogs Entries by Categories

Blog Tags:

* Can be used in order to search for blogs by keywords


Awared MVP


Microsoft® Community Contributor 

Microsoft® Community Contributor

 Read this before you use the blog! Maximize

Recent Entries


Written by: ronen ariely
11/09/2014 12:01 RssIcon

This short blog gives a simple example of how to use "FOR XML" hint in order to consolidate data in different rows into a single row in the result SET (an operation known as Un-Split data). In this example we are going to group together only rows with the same value in specific column/s. In short we can call this action "Un-split data with grouping"

* Usually I do not like to write about something that already other wrote and can be found online in one version or another, but since there is currently a temporarily problem in forums to upload file, I bring the solution to a question that was asked there in this short post.

-- Unsplit Using For XML with group by columns
/******************************************************************* DDL */
create table events(EventType int, Date date, Cost numeric(8,2))
/******************************************************************* DML */
insert into events
values      (1, '1/1/2015', 100),
            (1, '1/1/2016', 200),
            (1, '1/1/2016', 300),
            (1, '1/1/2017', 300),
            (2, '1/1/2015', 300),
            (2, '1/1/2016', 300),
            (2, '1/1/2016', 300),
            (3, '1/1/2015', 250)
/******************************************************************* Ceck data */
select * from events
/******************************************************************* Solution */
            SELECT ', Date: ' + CAST([Date] AS VARCHAR(MAX)) + ': Cost: ' + CAST([Cost] AS VARCHAR(MAX))
            FROM events
            WHERE (EventType = StudentCourses.EventType)
            FOR XML PATH ('')
    ) AS NameValues
FROM events StudentCourses
GROUP BY EventType

I hope this is useful :-)

---- Update July 25 2018 ----

several days ago I gave a link to this post, but from the time this post was written there was a new function STRING_AGG that added in 2017. If you are using SQL Server 2017 and above then you can use bellow simple solution:

    N'Date:' + ISNULL(CONVERT(NVARCHAR(10),[Date]),'') + N'; Cost: ' + ISNULL(CONVERT(NVARCHAR(10),[Cost]),'')
    , ','
) WITHIN GROUP (order by EventType)
from events
group by EventType