TSQL to Rollup multiple rows into single row and column

In my team, we mange multiple Applications and Servers. As part of Application migrations, Server consolidations, failovers application Server mapping will always be changing. I always depend on Application Server mapping during analysis but my major challenge was with multiple rows for each app in traditional way. I had to do some research to learn about XML PATH to rollup multiple rows into single row based on specific column.

Considering the fact that I have to deal with hundreds of Servers and 50+ Apps, this simple method is a life saver 🙂

Following is simplified example, I know this is not efficient but I am using this code for Demo only!

-- Preparing sample data
create table [Servers] (ServerID int, ServerName varchar(60), AdditionalInfo varchar(200))
create table Apps (AppID int, AppName varchar(60), AdditionalInfo varchar(200))
create table AppsServerMapping (AppID int, ServerID int, IsActive bit)

insert into [Servers] values(1, 'Web01', '')
insert into [Servers] values(2, 'Web02', '')
insert into [Servers] values(3, 'Web03', '')
insert into [Servers] values(4, 'Web04', '')
insert into [Servers] values(5, 'SQL01', '')
insert into [Servers] values(6, 'SQL02', '')
insert into [Servers] values(7, 'SQL03', '')
insert into [Servers] values(8, 'SQL04', '')
insert into [Servers] values(9, 'SQL05', '')
insert into [Servers] values(10, 'SQL06', '')

Insert into Apps values(1, 'App1', '')
Insert into Apps values(2, 'App2', '')
Insert into Apps values(3, 'App3', '')
Insert into Apps values(4, 'App4', '')

Insert into AppsServerMapping values(1,1,1)
Insert into AppsServerMapping values(1,5,1)
Insert into AppsServerMapping values(1,6,1)
Insert into AppsServerMapping values(1,9,1)
Insert into AppsServerMapping values(2,2,1)
Insert into AppsServerMapping values(2,5,1)
Insert into AppsServerMapping values(3,3,1)
Insert into AppsServerMapping values(3,4,1)
Insert into AppsServerMapping values(3,7,1)
Insert into AppsServerMapping values(3,8,1)
Insert into AppsServerMapping values(3,9,1)
Insert into AppsServerMapping values(4,2,1)
Insert into AppsServerMapping values(4,6,1)
Insert into AppsServerMapping values(4,9,1)


-- Traditional way of checking all Servers related to App2, App3 
select a.AppName, s.ServerName from AppsServerMapping map (nolock)
join [Servers] s (nolock) on s.ServerID = map.ServerID
join Apps a (nolock) on a.AppID = map.AppID
where s.ServerName in ('')
or a.AppName in ('App2', 'App3')
and map.IsActive = 1 
order by 1, 2


-- Finding all Servers mapped to App2, and App3 using XML PATH option 
;WITH CTETable as
(select a.AppName, s.ServerName from AppsServerMapping map (nolock)
join [Servers] s (nolock) on s.ServerID = map.ServerID
join Apps a (nolock) on a.AppID = map.AppID
where s.ServerName in ('')
or a.AppName in ('App2', 'App3')
and map.IsActive = 1 )
select c1.AppName, 
stuff((select ';' + c2.ServerName from CTETable C2
where c2.AppName = c1.AppName
order by c2.ServerName
FOR XML PATH('')),1,1,'') as AllServers
from CTETable c1
group by c1.AppName

FYI: Results…
App_Server_Mapping_Results

NOTE: UNICODE characters can not be managed in XML, make sure not to use XML for handling UNICODE characters.

-Vijred

Advertisements
This entry was posted in Productivity, Script, SQL, SQL Query and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s