Resultset in a format

  • VSSGeorge

    SSCrazy Eights

    Points: 8144

    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.

     

  • fahey.jonathan

    Hall of Fame

    Points: 3566

    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


    WITH BaseData
    AS (
    SELECT NationalityID,
    NationalityName,
    Units,
    SortSeq = ROW_NUMBER() OVER (ORDER BY Units DESC)
    FROM #NationalityMaster
    )
    SELECT NationalityID,
    NationalityName,
    Units,
    SortSeq
    FROM BaseData
    WHERE SortSeq <= 4

    UNION ALL

    SELECT NULL,
    'Other',
    Units = SUM(Units),
    SortSeq = 9
    FROM BaseData
    WHERE SortSeq > 4
    ORDER BY SortSeq

    • This reply was modified 1 month 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 2 (of 2 total)

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