Nice Tidy SQL problem & Solution

Have been writing a couple of tech articles in the recent past (If I had started writing it on a regualr basis I might have had an encyclopedia by now.... I should write something else just 4 a change huh!!!):
Question posted on one of the SQL server sites out there--> concatenation of rows to columns....
There are 3 ways of accomplishing it and I ain't going thru all those approaches but will just highlight them (except for my solution here):-
1. Creating a dynamic SQL which will keep concatenating the rows into a specific column in a PL/SQL block which returns the required table.

2. Using recursive CTE to concatenate the rows into columns .... Maybe should hit upon CTE's which are extremely useful in a later post (If I get some time i.e.)

3. Now this method is pretty cool Now let me give the problem statement as posted by that individual...

Let us create the table in the following manner-->
select 'Report1' as reportID,'Browser' as RoleID, 'YChen' as User2
into #tbl
UNION
select 'Report1' as reportID,'Browser' as RoleID,'AChen' as User2
UNION
select 'Report1' as reportID,'Browser' as RoleID,'BChen' as User2
UNION
select 'Report2' as reportID,'Browser' as RoleID,'YChen' as User2
UNION
select 'Report2' as reportID,'Browser' as RoleID,'XChen' as User2
UNION
select 'Report2' as reportID,'Browser' as RoleID,'BChen' as User2
UNION
select 'Report1' as reportID,'Writer' as RoleID,'YChen' as User2
UNION
select 'Report1' as reportID,'Writer' as RoleID,'XChen' as User2

When you execute the following sql you will get the following results:-
SQL> select * from #tbl


Now comes the part where one would want to concatenate the table in the following manner (a sample row)-->








reportidConcatenatedColumn

Report2

BChen XChen YChen



So this is what I did--> I utililized the FOR XML clause in the sql query to generate an XML data set for the column that required to be concatenated in the following manner:-
select reportid,replace(replace(Val,'<User2>',''),'</User2>','')
from
(select distinct a.reportid,
(select distinct User2+' ' from #tbl c where c.reportid = a.reportid FOR XML PATH('')) as Val
from #tbl a) x

Results:


More details about the FOR XML is defined in the following link:
FOR XML

And that ends a nice problem with a bang!

Comments

Popular posts from this blog

System.ConfigurationSettings.AppSettings is Obsolete

Branding your SharePoint site in a super fast way - Emgage

Sharepoint & SSRS integration Issues