en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

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

Search in blogs


Blog Categories:

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

Blog Tags:

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

TNWikiSummit


Awared MVP 


 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize
ספט11

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))
go
 
/******************************************************************* 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)
GO
 
/******************************************************************* Ceck data */
select * from events
GO
 
/******************************************************************* Solution */
SELECT
    EventType,
    STUFF(
        (
            SELECT ', Date: ' + CAST([Date] AS VARCHAR(MAX)) + ': Cost: ' + CAST([Cost] AS VARCHAR(MAX))
            FROM events
            WHERE (EventType = StudentCourses.EventType)
            FOR XML PATH ('')
        ),1,2,''
    ) AS NameValues
FROM events StudentCourses
GROUP BY EventType
GO


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:

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