Post reply

Resultset in a format

  • February 26, 2020 at 6:45 pm

    #3729024

    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

     I want this 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 achieved.

     

  • February 27, 2020 at 3:24 pm

    #3729298

    Thank you for providing the table definitions, data, and code to produce the results.  However, executing the code gives the following error: Invalid object name 'EmployeeMemberMaster'.  That table needs to be provided in order to give a proper response.

    I made a small change to one of your sample tables, and used that as the basis of an example.  Hopefully you can use this example to construct what you need.

    drop table #NationalityMaster

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


    WITHBaseData
    AS(
    SELECTNationalityID,
    NationalityName,
    Units,
    SortSeq= ROW_NUMBER() OVER (ORDER BY Units DESC)
    FROM#NationalityMaster
    )
    SELECTNationalityID,
    NationalityName,
    Units,
    SortSeq
    FROMBaseData
    WHERESortSeq <= 4

    UNIONALL

    SELECTNULL,
    'Other',
    Units= SUM(Units),
    SortSeq= 9
    FROMBaseData
    WHERESortSeq > 4
    ORDER BY SortSeq

    • This reply was modified 4 years, 8 months ago by  fahey.jonathan. Reason: Changed ROW_NUMBER sort to DESC to show the largest 4, not the smallest 4

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply