SQLTeam.com | Weblogs | Forums

Getting top records resultset in a specified format

I have the following tables:

create table #EmployeeSystemsMaster(EmployeeSystemID int, EmployeeSystemName nvarchar(150))
insert into #EmployeeSystemsMaster
select 1, 'Core Banking' union
select 2, 'Treasury' union
select 3, 'Retail Banking' union
select 4, 'Corporate Banking' union
select 5, 'Anti Money Laundering' union
select 6, 'Trade Finance' 

select * From #EmployeeSystemsMaster

create table #NationalityMaster(NationalityID int, NationalityName nvarchar(150))
insert into #NationalityMaster
select 1, 'Saudi Arabia' union
select 2, 'India' union
select 3, 'United States' union
select 4, 'United Kingdom' union
select 5, 'Canada' union
select 6, 'Australia' union
select 7, 'Singapore' 

select * from #NationalityMaster

create table #EmployeeMaster(EmployeeID int identity(1,1), EmployeeName nvarchar(250), NationalityID int, EmployeeSystemID int)
insert into #EmployeeMaster (EmployeeName,NationalityID,EmployeeSystemID)
select 'IQ', 1, 1 union
select 'JK', 3, 6 union
select 'JOP',1, 4 union
select 'IPO', 4, 3 union
select 'RERT', 1, 2 union
select 'GT',1, 2 union
select 'Kakka', 1, 1 union
select 'Wala Weber', 1, 1 union
select 'Coffee Bean', 1, 1 

select * from #EmployeeMaster

I am using the following query to get results in the format below:

DECLARE @tempSystemMaster TABLE
	(		 
			 NationalityID					bigint NULL
			,NationalityName				NVARCHAR(256)
			,SystemsCount 				    bigint NULL
			,CoreBanking 					bigint NULL
			,Treasury 					    bigint NULL
			,RetailBanking 					bigint NULL
			,CorporateBanking				bigint NULL
			,AntiMoneyLaundering 			bigint NULL
			,TradeFinance 					bigint NULL
	);


		SELECT E.NationalityID, count(E.EmployeeSystemID) AS SystemsCount	
		INTO #TmpNatMaster
		FROM EmployeeMemberMaster E
		INNER JOIN EmployeeSystemsMaster S ON E.EmployeeSystemID = S.EmployeeSystemID
		WHERE ISNULL(E.IsDeleted,0) = 0 
		GROUP BY E.NationalityID;

		SELECT 
			 N.NationalityID
			,N.NationalityName AS NationalityName
			,ISNULL(T.SystemsCount,0) as SystemsCount	
		into #tempSysTypes		
		from #TmpNatMaster T
		FULL JOIN NationalityMaster N ON N.NationalityID = T.NationalityID
		order by N.NationalityID ASC;

		MERGE @tempSystemMaster AS T
		USING(SELECT NationalityID,NationalityName,SystemsCount from #tempSysTypes) AS S 
			 (NationalityID,NationalityName,SystemsCount)
		ON   (T.NationalityID=S.NationalityID)

		WHEN NOT MATCHED THEN
		INSERT(NationalityID,NationalityName,SystemsCount)
		VALUES(S.NationalityID,S.NationalityName,S.SystemsCount)
		WHEN MATCHED THEN
		UPDATE SET 
		T.NationalityName		= ISNULL(S.NationalityName,T.NationalityName),
		T.SystemsCount		= ISNULL(S.SystemsCount,T.SystemsCount);

		--Sys:CoreBanking 
		SELECT  
		N.NationalityID, Count(S.EmployeeSystemID) as CoreBanking 
		into #CoreBanking
		FROM EmployeeMemberMaster E
		INNER JOIN EmployeeSystemsMaster S ON E.EmployeeSystemID = S.EmployeeSystemID and S.EmployeeSystemID = 1
		FULL JOIN NationalityMaster N ON N.NationalityID = E.NationalityID 
		where ISNULL(E.IsDeleted,0) = 0 
		GROUP BY N.NationalityID

		MERGE @tempSystemMaster AS T
		USING(SELECT NationalityID,CoreBanking from #CoreBanking) AS S 
		(NationalityID,CoreBanking)
		ON (T.NationalityID=S.NationalityID)
		WHEN NOT MATCHED THEN
		INSERT(NationalityID,CoreBanking)
		VALUES(S.NationalityID,S.CoreBanking)
		WHEN MATCHED THEN
		UPDATE SET 
		T.CoreBanking	= ISNULL(S.CoreBanking,T.CoreBanking);

		--Sys:Treasury 
		SELECT  
		N.NationalityID, Count(S.EmployeeSystemID) as Treasury 
		into #Treasury
		FROM EmployeeMemberMaster E
		INNER JOIN EmployeeSystemsMaster S ON E.EmployeeSystemID = S.EmployeeSystemID and S.EmployeeSystemID = 2
		FULL JOIN NationalityMaster N ON N.NationalityID = E.NationalityID 
		where ISNULL(E.IsDeleted,0) = 0 
		GROUP BY N.NationalityID

		MERGE @tempSystemMaster AS T
		USING(SELECT NationalityID,Treasury from #Treasury) AS S 
		(NationalityID,Treasury)
		ON (T.NationalityID=S.NationalityID)
		WHEN NOT MATCHED THEN
		INSERT(NationalityID,Treasury)
		VALUES(S.NationalityID,S.Treasury)
		WHEN MATCHED THEN
		UPDATE SET 
		T.Treasury	= ISNULL(S.Treasury,T.Treasury);

		--Sys:RetailBanking 
		SELECT  
		N.NationalityID, Count(S.EmployeeSystemID) as RetailBanking 
		into #RetailBanking
		FROM EmployeeMemberMaster E
		INNER JOIN EmployeeSystemsMaster S ON E.EmployeeSystemID = S.EmployeeSystemID and S.EmployeeSystemID = 3
		FULL JOIN NationalityMaster N ON N.NationalityID = E.NationalityID 
		where ISNULL(E.IsDeleted,0) = 0 
		GROUP BY N.NationalityID

		MERGE @tempSystemMaster AS T
		USING(SELECT NationalityID,RetailBanking from #RetailBanking) AS S 
		(NationalityID,RetailBanking)
		ON (T.NationalityID=S.NationalityID)
		WHEN NOT MATCHED THEN
		INSERT(NationalityID,RetailBanking)
		VALUES(S.NationalityID,S.RetailBanking)
		WHEN MATCHED THEN
		UPDATE SET 
		T.RetailBanking	= ISNULL(S.RetailBanking,T.RetailBanking);

		--Sys:CorporateBanking 
		SELECT  
		N.NationalityID, Count(S.EmployeeSystemID) as CorporateBanking 
		into #CorporateBanking
		FROM EmployeeMemberMaster E
		INNER JOIN EmployeeSystemsMaster S ON E.EmployeeSystemID = S.EmployeeSystemID and S.EmployeeSystemID = 4
		FULL JOIN NationalityMaster N ON N.NationalityID = E.NationalityID 
		where ISNULL(E.IsDeleted,0) = 0 
		GROUP BY N.NationalityID

		MERGE @tempSystemMaster AS T
		USING(SELECT NationalityID,CorporateBanking from #CorporateBanking) AS S 
		(NationalityID,CorporateBanking)
		ON (T.NationalityID=S.NationalityID)
		WHEN NOT MATCHED THEN
		INSERT(NationalityID,CorporateBanking)
		VALUES(S.NationalityID,S.CorporateBanking)
		WHEN MATCHED THEN
		UPDATE SET 
		T.CorporateBanking	= ISNULL(S.CorporateBanking,T.CorporateBanking);

		--Sys:AntiMoneyLaundering 
		SELECT  
		N.NationalityID, Count(S.EmployeeSystemID) as AntiMoneyLaundering 
		into #AntiMoneyLaundering
		FROM EmployeeMemberMaster E
		INNER JOIN EmployeeSystemsMaster S ON E.EmployeeSystemID = S.EmployeeSystemID and S.EmployeeSystemID = 5
		FULL JOIN NationalityMaster N ON N.NationalityID = E.NationalityID 
		where ISNULL(E.IsDeleted,0) = 0 
		GROUP BY N.NationalityID

		MERGE @tempSystemMaster AS T
		USING(SELECT NationalityID,AntiMoneyLaundering from #AntiMoneyLaundering) AS S 
		(NationalityID,AntiMoneyLaundering)
		ON (T.NationalityID=S.NationalityID)
		WHEN NOT MATCHED THEN
		INSERT(NationalityID,AntiMoneyLaundering)
		VALUES(S.NationalityID,S.AntiMoneyLaundering)
		WHEN MATCHED THEN
		UPDATE SET 
		T.AntiMoneyLaundering	= ISNULL(S.AntiMoneyLaundering,T.AntiMoneyLaundering);

		--Sys:TradeFinance 
		SELECT  
		N.NationalityID, Count(S.EmployeeSystemID) as TradeFinance 
		into #TradeFinance
		FROM EmployeeMemberMaster E
		INNER JOIN EmployeeSystemsMaster S ON E.EmployeeSystemID = S.EmployeeSystemID and S.EmployeeSystemID = 6
		FULL JOIN NationalityMaster N ON N.NationalityID = E.NationalityID 
		where ISNULL(E.IsDeleted,0) = 0 
		GROUP BY N.NationalityID

		MERGE @tempSystemMaster AS T
		USING(SELECT NationalityID,TradeFinance from #TradeFinance) AS S 
		(NationalityID,TradeFinance)
		ON (T.NationalityID=S.NationalityID)
		WHEN NOT MATCHED THEN
		INSERT(NationalityID,TradeFinance)
		VALUES(S.NationalityID,S.TradeFinance)
		WHEN MATCHED THEN
		UPDATE SET 
		T.TradeFinance	= ISNULL(S.TradeFinance,T.TradeFinance);

		SELECT 
			 NationalityID					
			,NationalityName				
			,SystemsCount 				   
			,CoreBanking 					
			,Treasury 					    
			,RetailBanking 					
			,CorporateBanking				
			,AntiMoneyLaundering 			
			,TradeFinance 
			FROM @tempSystemMaster

DROP TABLE #TmpNatMaster
DROP TABLE #tempSysTypes
DROP TABLE #CoreBanking
DROP TABLE #Treasury
DROP TABLE #RetailBanking
DROP TABLE #CorporateBanking
DROP TABLE #AntiMoneyLaundering
DROP TABLE #TradeFinance

The resultset I am getting is below:

NationalityID NationalityName SystemsCount CoreBanking Treasury RetailBanking CorporateBanking AntiMoneyLaundering TradeFinance
1 SaudiArabia 7 4 2 0 1 0 0
3 UnitedStates 1 0 0 0 0 0 1
4 UnitedKingdom 1 0 0 1 0 0 0
2 India 0 0 0 0 0 0 0
5 Canada 0 0 0 0 0 0 0
6 Australia 0 0 0 0 0 0 0
7 Singapore 0 0 0 0 0 0 0

In addition to this, I want the resultset to shown in a format such that TOP 4 countries & their respective counts are shown like this above & the remaining countries are shown as "Others" with their counts are summed and shown in a single line.For example, here the nations, Canada, Australia & singapore should be shown as "others" with their counts summed to shown in one line. Please help me how it can be done.

The query you supplied does not work since we don't have the tables that you have. I modified your query to use the temp tables you laid out and removed the IsDeleted criteria since that wasn't provided either

DECLARE @tempSystemMaster TABLE
	(		 
			 NationalityID					bigint NULL
			,NationalityName				NVARCHAR(256)
			,SystemsCount 				    bigint NULL
			,CoreBanking 					bigint NULL
			,Treasury 					    bigint NULL
			,RetailBanking 					bigint NULL
			,CorporateBanking				bigint NULL
			,AntiMoneyLaundering 			bigint NULL
			,TradeFinance 					bigint NULL
	);


		SELECT E.NationalityID, count(E.EmployeeSystemID) AS SystemsCount	
		INTO #TmpNatMaster
		FROM #EmployeeMaster E
		INNER JOIN #EmployeeSystemsMaster S ON E.EmployeeSystemID = S.EmployeeSystemID
		GROUP BY E.NationalityID;

		SELECT 
			 N.NationalityID
			,N.NationalityName AS NationalityName
			,ISNULL(T.SystemsCount,0) as SystemsCount	
		into #tempSysTypes		
		from #TmpNatMaster T
		FULL JOIN #NationalityMaster N ON N.NationalityID = T.NationalityID
		order by N.NationalityID ASC;

		MERGE @tempSystemMaster AS T
		USING(SELECT NationalityID,NationalityName,SystemsCount from #tempSysTypes) AS S 
			 (NationalityID,NationalityName,SystemsCount)
		ON   (T.NationalityID=S.NationalityID)

		WHEN NOT MATCHED THEN
		INSERT(NationalityID,NationalityName,SystemsCount)
		VALUES(S.NationalityID,S.NationalityName,S.SystemsCount)
		WHEN MATCHED THEN
		UPDATE SET 
		T.NationalityName		= ISNULL(S.NationalityName,T.NationalityName),
		T.SystemsCount		= ISNULL(S.SystemsCount,T.SystemsCount);

		--Sys:CoreBanking 
		SELECT  
		N.NationalityID, Count(S.EmployeeSystemID) as CoreBanking 
		into #CoreBanking
		FROM #EmployeeMaster E
		INNER JOIN #EmployeeSystemsMaster S ON E.EmployeeSystemID = S.EmployeeSystemID and S.EmployeeSystemID = 1
		FULL JOIN #NationalityMaster N ON N.NationalityID = E.NationalityID 
		--where ISNULL(E.IsDeleted,0) = 0 
		GROUP BY N.NationalityID

		MERGE @tempSystemMaster AS T
		USING(SELECT NationalityID,CoreBanking from #CoreBanking) AS S 
		(NationalityID,CoreBanking)
		ON (T.NationalityID=S.NationalityID)
		WHEN NOT MATCHED THEN
		INSERT(NationalityID,CoreBanking)
		VALUES(S.NationalityID,S.CoreBanking)
		WHEN MATCHED THEN
		UPDATE SET 
		T.CoreBanking	= ISNULL(S.CoreBanking,T.CoreBanking);

		--Sys:Treasury 
		SELECT  
		N.NationalityID, Count(S.EmployeeSystemID) as Treasury 
		into #Treasury
		FROM #EmployeeMaster E
		INNER JOIN #EmployeeSystemsMaster S ON E.EmployeeSystemID = S.EmployeeSystemID and S.EmployeeSystemID = 2
		FULL JOIN #NationalityMaster N ON N.NationalityID = E.NationalityID 
		--where ISNULL(E.IsDeleted,0) = 0 
		GROUP BY N.NationalityID

		MERGE @tempSystemMaster AS T
		USING(SELECT NationalityID,Treasury from #Treasury) AS S 
		(NationalityID,Treasury)
		ON (T.NationalityID=S.NationalityID)
		WHEN NOT MATCHED THEN
		INSERT(NationalityID,Treasury)
		VALUES(S.NationalityID,S.Treasury)
		WHEN MATCHED THEN
		UPDATE SET 
		T.Treasury	= ISNULL(S.Treasury,T.Treasury);

		--Sys:RetailBanking 
		SELECT  
		N.NationalityID, Count(S.EmployeeSystemID) as RetailBanking 
		into #RetailBanking
		FROM #EmployeeMaster E
		INNER JOIN #EmployeeSystemsMaster S ON E.EmployeeSystemID = S.EmployeeSystemID and S.EmployeeSystemID = 3
		FULL JOIN #NationalityMaster N ON N.NationalityID = E.NationalityID 
		--where ISNULL(E.IsDeleted,0) = 0 
		GROUP BY N.NationalityID

		MERGE @tempSystemMaster AS T
		USING(SELECT NationalityID,RetailBanking from #RetailBanking) AS S 
		(NationalityID,RetailBanking)
		ON (T.NationalityID=S.NationalityID)
		WHEN NOT MATCHED THEN
		INSERT(NationalityID,RetailBanking)
		VALUES(S.NationalityID,S.RetailBanking)
		WHEN MATCHED THEN
		UPDATE SET 
		T.RetailBanking	= ISNULL(S.RetailBanking,T.RetailBanking);

		--Sys:CorporateBanking 
		SELECT  
		N.NationalityID, Count(S.EmployeeSystemID) as CorporateBanking 
		into #CorporateBanking
		FROM #EmployeeMaster E
		INNER JOIN #EmployeeSystemsMaster S ON E.EmployeeSystemID = S.EmployeeSystemID and S.EmployeeSystemID = 4
		FULL JOIN #NationalityMaster N ON N.NationalityID = E.NationalityID 
		--where ISNULL(E.IsDeleted,0) = 0 
		GROUP BY N.NationalityID

		MERGE @tempSystemMaster AS T
		USING(SELECT NationalityID,CorporateBanking from #CorporateBanking) AS S 
		(NationalityID,CorporateBanking)
		ON (T.NationalityID=S.NationalityID)
		WHEN NOT MATCHED THEN
		INSERT(NationalityID,CorporateBanking)
		VALUES(S.NationalityID,S.CorporateBanking)
		WHEN MATCHED THEN
		UPDATE SET 
		T.CorporateBanking	= ISNULL(S.CorporateBanking,T.CorporateBanking);

		--Sys:AntiMoneyLaundering 
		SELECT  
		N.NationalityID, Count(S.EmployeeSystemID) as AntiMoneyLaundering 
		into #AntiMoneyLaundering
		FROM #EmployeeMaster E
		INNER JOIN #EmployeeSystemsMaster S ON E.EmployeeSystemID = S.EmployeeSystemID and S.EmployeeSystemID = 5
		FULL JOIN #NationalityMaster N ON N.NationalityID = E.NationalityID 
		--where ISNULL(E.IsDeleted,0) = 0 
		GROUP BY N.NationalityID

		MERGE @tempSystemMaster AS T
		USING(SELECT NationalityID,AntiMoneyLaundering from #AntiMoneyLaundering) AS S 
		(NationalityID,AntiMoneyLaundering)
		ON (T.NationalityID=S.NationalityID)
		WHEN NOT MATCHED THEN
		INSERT(NationalityID,AntiMoneyLaundering)
		VALUES(S.NationalityID,S.AntiMoneyLaundering)
		WHEN MATCHED THEN
		UPDATE SET 
		T.AntiMoneyLaundering	= ISNULL(S.AntiMoneyLaundering,T.AntiMoneyLaundering);

		--Sys:TradeFinance 
		SELECT  
		N.NationalityID, Count(S.EmployeeSystemID) as TradeFinance 
		into #TradeFinance
		FROM #EmployeeMaster E
		INNER JOIN #EmployeeSystemsMaster S ON E.EmployeeSystemID = S.EmployeeSystemID and S.EmployeeSystemID = 6
		FULL JOIN #NationalityMaster N ON N.NationalityID = E.NationalityID 
		--where ISNULL(E.IsDeleted,0) = 0 
		GROUP BY N.NationalityID

		MERGE @tempSystemMaster AS T
		USING(SELECT NationalityID,TradeFinance from #TradeFinance) AS S 
		(NationalityID,TradeFinance)
		ON (T.NationalityID=S.NationalityID)
		WHEN NOT MATCHED THEN
		INSERT(NationalityID,TradeFinance)
		VALUES(S.NationalityID,S.TradeFinance)
		WHEN MATCHED THEN
		UPDATE SET 
		T.TradeFinance	= ISNULL(S.TradeFinance,T.TradeFinance);

		SELECT 
			 NationalityID					
			,NationalityName				
			,SystemsCount 				   
			,CoreBanking 					
			,Treasury 					    
			,RetailBanking 					
			,CorporateBanking				
			,AntiMoneyLaundering 			
			,TradeFinance 
			into #tempSystemMaster
			FROM @tempSystemMaster

query:

declare @TopRows int = 4
;with cte as (
Select 	NationalityID,NationalityName ,SystemsCount,CoreBanking,Treasury ,RetailBanking ,CorporateBanking,AntiMoneyLaundering ,TradeFinance ,
		row_number() over (order by SystemsCount desc, NationalityID) as Ranking
 from #tempSystemMaster)
 
select 	NationalityName ,SystemsCount,CoreBanking,Treasury ,RetailBanking ,CorporateBanking,AntiMoneyLaundering ,TradeFinance
  from cte 
 where Ranking <= @TopRows
 union all
 select 	'Others' as NationalityName ,
			sum(SystemsCount),
			sum(CoreBanking),
			sum(Treasury ),
			sum(RetailBanking) ,
			sum(CorporateBanking),
			sum(AntiMoneyLaundering) ,
			sum(TradeFinance)
  from cte 
 where Ranking > @TopRows
2 Likes