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.