none
SQL to find Many-to-Many combinations of two columns RRS feed

  • Question

  • Hi,

    I am working on a Product Catalogue migration from System A to System B. I have a table with two columns that has all possible combinations of the products (there 257 rows)

    I would like to find all of the combinations where there is a Many-to-Many (M2M) combination between the values.

    For example:

    SysA   SysB
    A        Y
    A        Z
    B        Z

    i.e.

    Product A has 1:M relationship with Products Y & Z

    Product Z has 1:M relationship with Product A & B

    Which means there is therefore a M:M relationship between the two columns.

    Is there a way to extract all M:M combinations?

    Monday, June 10, 2019 5:49 PM

Answers

  • Hi Amir,

    We both are looking at same data differently 8)

    Here is the modified version of the query filtered to show PKO_Count(PKI) records. Here M:N are actual ratio numbers

    WITH map
    AS (
    	SELECT *,
    		(
    			SELECT CASE count(*)
    					WHEN 0
    						THEN '1:0'
    					WHEN 1
    						THEN '1:1'
    					ELSE '1:'+cast(count(*) AS varchar(3))
    					END
    			FROM SampleData
    			WHERE PKO = sd.PKO
    			) PKO_PKI,
    		(
    			SELECT CASE count(*)
    					WHEN 0
    						THEN '0:1'
    					WHEN 1
    						THEN '1:1'
    					ELSE cast(count(*) AS varchar(3))+':1'
    					END
    			FROM SampleData
    			WHERE PKI = sd.PKI
    			) PKI_PKO
    	FROM SampleData sd
    	)
    ,final_map AS (
    SELECT PKO,
    	PKI,
    	PKO_PKI,
    	PKI_PKO,
    	CASE 
    		WHEN right(PKO_PKI,1) > 1
    			AND left(PKI_PKO,1) > 1
    			THEN left(PKI_PKO,1)+':'+right(PKO_PKI,1)
    		ELSE NULL
    		END Many_to_Many
    FROM map)
    SELECT *
    FROM final_map
    WHERE pko_pki <> '1:1'
    ORDER BY PKO;WITH map
    AS (
    	SELECT *,
    		(
    			SELECT CASE count(*)
    					WHEN 0
    						THEN '1:0'
    					WHEN 1
    						THEN '1:1'
    					ELSE '1:'+cast(count(*) AS varchar(3))
    					END
    			FROM SampleData
    			WHERE PKO = sd.PKO
    			) PKO_PKI,
    		(
    			SELECT CASE count(*)
    					WHEN 0
    						THEN '0:1'
    					WHEN 1
    						THEN '1:1'
    					ELSE cast(count(*) AS varchar(3))+':1'
    					END
    			FROM SampleData
    			WHERE PKI = sd.PKI
    			) PKI_PKO
    	FROM SampleData sd
    	)
    ,final_map AS (
    SELECT PKO,
    	PKI,
    	PKO_PKI,
    	PKI_PKO,
    	CASE 
    		WHEN right(PKO_PKI,1) > 1
    			AND left(PKI_PKO,1) > 1
    			THEN left(PKI_PKO,1)+':'+right(PKO_PKI,1)
    		ELSE NULL
    		END Many_to_Many
    FROM map)
    SELECT *
    FROM final_map
    WHERE pko_pki <> '1:1'
    ORDER BY PKO;

    Here is the mapping from your output to mine. Hope that makes sense!

    Thursday, June 13, 2019 5:52 PM

All replies

  • Hi Amir,

    If I understood you correctly, you wanted to find if two products have many to many relations. I took a small example, where:

    1. A --> Y,Z (A has 1:M relation with Y,Z)
    2. B --> Z   (B has 1:1 relation with Z)
    3. C --> U,V (C has 1:M with U,V

    It is clear from (1) that A has 1:M relationship

    From (1) and (2), it is clear that Z has 1:M relationship

    so A and Z both have M:M relationship

    Here is the small query to find out M:M relations between products

    CREATE TABLE Systems(SysA char(1),SysB char(1));
    INSERT INTO Systems VALUES ('A','Y'),('A','Z'),('B','Z'),('C','U'),('C','V');
    
    WITH SysA
    AS (
    	SELECT SysA,
    		COUNT(*) NumSystems
    	FROM systems
    	GROUP BY SysA
    	HAVING count(*) > 1
    	),
    SysB
    AS (
    	SELECT SysB,
    		COUNT(*) NumSystems
    	FROM systems
    	GROUP BY SysB
    	HAVING count(*) > 1
    	)
    SELECT s.SysA,
    	s.SysB
    FROM systems s
    INNER JOIN SysA sa ON s.sysa = sa.SysA
    INNER JOIN SysB sb ON s.sysb = sb.SysB;
    

    Monday, June 10, 2019 6:41 PM
  • Hi amir tohidi,

     

    Please try following script.

     
    IF OBJECT_ID('Systems') IS NOT NULL drop table  Systems
    go 
    CREATE TABLE Systems(SysA char(1),SysB char(1))
    INSERT INTO Systems VALUES 
    ('A','Y'),('A','Z'),('B','Z')
    
    ;with cte as (
    select *, dense_rank()over(partition by SysA order by SysB) count_SysA,
    dense_rank()over(partition by SysB order by SysA)  count_SysB
    from Systems)
    ,cte1 as (
    select distinct SysA from cte where count_SysA>1 )
    ,cte2 as (
    select distinct SysB from cte where count_SysB>1 )
    select * from Systems a
    where exists (select * from cte1 where SysA=a.SysA)
    and exists (select * from cte2 where SysB=a.SysB)
    /*
    SysA SysB
    ---- ----
    A    Z
    */
    
    

    Hope it will help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, June 11, 2019 3:05 AM
  • I have tried both solutions on our data and can't get the expected result.

    I will try to figure out what is wrong and get back to you.

    Tuesday, June 11, 2019 5:40 PM
  • Declare @SysA Table(ColumnA Varchar(100))
    Declare @SysB Table(ColumnB Varchar(100))


    insert into @SysA
    Values
    ('A'),('B')
    insert into @SysB
    Values
    ('Y'),('Z')


    Select * from @SysA
    Cross join @SysB

    mohammad waheed

    Tuesday, June 11, 2019 7:41 PM
  •  

    Hi amir tohidi,

    Thank you for your kind reply.

     

    We will wait for you back. If you have any question or more information , please let me know .

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, June 12, 2019 8:06 AM
  • I am afraid I can't get any of the above SQL to work. So, I have provided the DDL and DML below to create the Keys form the two systems (PKO and PKI columns) and the desired column Matrix that needs to show the following:

            PKI
    PKO  0:0      0:1     0:M
            1:0      1:1     1:M
            M:0      M:1    M:M

    The N:M notation is PKO:PKI 

    e.g. 1:M means one PKO maps to many PKI

    e.g. demand_premier_staff maps to 300_30007_01 and 300_30007_07
    and 300_30007_07 maps to demand_premier_staff and demand_premier_special

    CREATE TABLE [dbo].[SampleData](
    	[PKO] [varchar](32) NOT NULL,
    	[PKI] [varchar](12) NULL,
    	[Mapping] [varchar](10) NULL
    ) ON [PRIMARY]
    
    GO
    SET ANSI_PADDING OFF
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_asset30_basenp', N'200_20001_01', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_asset30_ifanp', N'200_20001_02', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_asset30_master', N'200_20001_03', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_asset30_nonpers', N'200_20001_04', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_ast30i2_basenp', N'200_20001_05', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_ast30i2_nonpers', N'200_20001_06', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_ast30i3_master', N'200_20001_03', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_ast30i3_nonpers', N'200_20001_07', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_bna95_basenp', N'999_99999_09', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_bna95_nonpers', N'200_20002_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_client_basenp', N'100_10001_01', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_client_master', N'100_10001_02', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_client_nelsmast', N'100_10001_02', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_client_nelsons', N'100_10001_03', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_client_nelsspec', N'100_10001_03', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_client_nonpers', N'100_10001_03', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_client_personal', N'999_99999_09', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_client_special', N'100_10001_03', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_corp_basenp', N'100_10002_01', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_corp_nonpers', N'100_10002_02', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_dollpba_personal', N'301_30101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_dollres_master', N'101_10101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_dollres_nonpers', N'101_10102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_europba_personal', N'301_30102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_eurores_master', N'101_10103_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_eurores_nonpers', N'101_10104_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_gtpba_personal', N'300_30002_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_HUB1_ifapers', N'300_30001_02', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_HUB1_personal', N'300_30001_01', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_HUB2_personal', N'300_30001_01', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_invest_alsaving', N'400_40001_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_invest_basenp', N'100_10003_01', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_invest_basep', N'300_30004_02', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_invest_bond', N'501_50103_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_invest_charity', N'100_10006_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_invest_feeder', N'999_99999_09', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_invest_isafix', N'999_99999_09', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_invest_isavar', N'600_60001_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_invest_jpmnp', N'999_99999_09', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_invest_master', N'100_10003_02', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_invest_nelsmast', N'100_10003_02', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_invest_nelsons', N'300_30004_03', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_invest_nelsons', N'300_30004_07', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_invest_nelsspec', N'999_99999_09', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_invest_nonpers', N'100_10003_03', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_invest_pension', N'999_99999_09', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_invest_personal', N'300_30004_05', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_invest_personal', N'300_30004_06', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_invest_sipp', N'100_10004_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_invest_special', N'300_30004_04', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_invest_staff', N'300_30004_01', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_invest_tessfix', N'999_99999_09', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_invest_tessgst', N'999_99999_09', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_invest_tessvar', N'999_99999_09', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_invest_trustee', N'999_99999_09', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_pna95_personal', N'400_40002_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_premier_basep', N'300_30007_02', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_premier_buscard', N'300_30003_02', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_premier_busmast', N'300_30003_01', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_premier_ifapers', N'300_30007_03', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_premier_jpmp', N'999_99999_09', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_premier_jpmvip', N'999_99999_09', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_premier_master', N'300_30007_04', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_premier_personal', N'300_30007_05', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_premier_personal', N'300_30007_06', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_premier_prestige', N'300_30005_01', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_premier_prestige', N'300_30005_02', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_premier_private', N'300_30006_01', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_premier_private', N'300_30006_02', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_premier_special', N'300_30007_07', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_premier_staff', N'300_30007_01', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_premier_staff', N'300_30007_07', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_reserve_basenp', N'100_10005_01', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_reserve_charity', N'100_10006_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_reserve_nonpers', N'100_10005_02', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_reserve_nonpers', N'100_10005_05', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_reserve_nonpers', N'100_10005_06', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_reserve_nonpers', N'100_10005_07', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_reserve_nonpers', N'100_10005_08', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_reserve_nonpers', N'100_10005_09', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_reserve_nonpers', N'100_10005_10', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_reserve_pension', N'100_10007_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_reserve_solic', N'100_10005_03', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_reserve_special', N'100_10005_04', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_reserve_trustee', N'100_10008_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_sov30_basep', N'400_40003_01', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_sov30_ifapers', N'400_40003_02', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_sov30_master', N'400_40003_03', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_sov30_personal', N'400_40003_04', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_sov30i2_personal', N'400_40003_05', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_sov30i3_ifapers', N'400_40003_06', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_sov30i3_personal', N'400_40003_07', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_sov30i3_sipp', N'200_20003_01', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_termat_nonpers', N'500_50001_01', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'demand_termat_personal', N'500_50001_02', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_ALIRP03_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_ALIRP03_FDISAC', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_ALIRP04_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_ALIRP04_FDISAC', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_ALIRP05_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_ALIRP05_FDISAC', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_ALIRP06_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_ALIRP06_FDISAC', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_ALIRP07_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_ALIRP07_FDISAC', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_BPP0001_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_EGP000011_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_EGP000011_FDISAC', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_EGP000012_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_EGP000012_FDISAC', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_EGP00014_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_EGP00014_FDISAC', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_EGP0007_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_EGP0007_FDISAC', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_EGP0008_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_EGP0008_FDISAC', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_EGP0009_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_EGP0009_FDISAC', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_EGP0010_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_EGP0010_FDISAC', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_GP000014_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_GP000014_FDISAC', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_GP000016_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_GP000016_FDISAC', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_GP00011_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_GP00011_FDISAC', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_GP00012_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_GP00012_FDISAC', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_GP00375015_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_GP00375015_FDISAC', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_GP00375016_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_GP00375016_FDISAC', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB0000001_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB0000002_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB0000002_FDISAS', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB0000003_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB0000003_FDISAS', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB0000004_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB0000007_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB0000010_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB0000012_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB0000014_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB0000017_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB0000018_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB0000022_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB0000023_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB0000025_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB0000027_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB0000029_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB0000029_FDISAC', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB0000031_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB0000032_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB0000032_FDISAC', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB0000033_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB0000033_FDISAC', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB0000034_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB0000034_FDISAC', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB0000035_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB0000036_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB0000036_FDISAC', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB0000037_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB0000037_FDISAS', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB0000038_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB0000038_FDISAC', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB0000039_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB0000039_FDISAC', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB0000040_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB0000040_FDISAC', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB0000041_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB0000041_FDISAC', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB0000042_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB0000042_FDISAC', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB004Y028_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB004Y030_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB006Y028_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB006Y028_FDISAS', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB006Y030_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB006Y030_FDISAS', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB375Y005_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB375Y006_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB375Y008_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB375Y009_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB375Y011_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB375Y013_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB375Y015_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB375Y016_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB375Y019_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB375Y020_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB375Y021_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB375Y024_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB375Y026_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB550Y005_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB550Y005_FDISAS', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB550Y006_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB550Y006_FDISAS', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB550Y008_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB550Y008_FDISAS', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB550Y009_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB550Y009_FDISAS', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB550Y011_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB550Y011_FDISAS', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB550Y013_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB550Y013_FDISAS', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB550Y015_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB550Y015_FDISAS', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB550Y016_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB550Y016_FDISAS', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB550Y019_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB550Y019_FDISAS', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB550Y020_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB550Y020_FDISAS', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB550Y021_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB550Y021_FDISAS', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB550Y024_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB550Y024_FDISAS', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB550Y026_FDDIR', N'501_50101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'sp_SPB550Y026_FDISAS', N'501_50102_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'term_term_BHALL1', N'999_99999_09', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'term_term_BHALL2', N'999_99999_09', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'term_term_CDV1P', N'999_99999_09', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'term_term_CDV1T', N'999_99999_09', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'term_term_CDV3P', N'999_99999_09', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'term_term_EUBS99', N'999_99999_09', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'term_term_EUSP99', N'999_99999_09', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'term_term_EUST12', N'999_99999_09', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'term_term_EUST36', N'999_99999_09', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'term_term_FRISA1', N'701_70101_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'term_term_GBBS99', N'999_99999_09', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'term_term_GBIM99', N'999_99999_09', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'term_term_GBIN99', N'702_70201_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'term_term_GBIQ99', N'999_99999_09', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'term_term_GBSP12', N'999_99999_09', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'term_term_GBSP99', N'999_99999_09', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'term_term_GBST12', N'703_70301_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'term_term_GBST12', N'704_70401_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'term_term_GBST12', N'705_70501_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'term_term_GBST12', N'706_70601_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'term_term_GBST36', N'703_70301_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'term_term_GBST36', N'704_70401_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'term_term_GBST36', N'706_70601_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'term_term_GBST36', N'707_70701_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'term_term_GBST36', N'708_70801_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'term_term_GBST99', N'999_99999_09', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'term_term_GBVA12', N'999_99999_09', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'term_term_JHAYTD', N'999_99999_09', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'term_term_JHSDA', N'999_99999_09', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'term_term_MATRIX', N'999_99999_09', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'term_term_PRIVAT', N'999_99999_09', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'term_term_SAGAP', N'709_70901_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'term_term_SAGAT', N'710_71001_00', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'term_term_SPECAL', N'999_99999_09', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'term_term_USBS99', N'999_99999_09', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'term_term_USIN99', N'999_99999_09', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'term_term_USSP12', N'999_99999_09', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'term_term_USSP99', N'999_99999_09', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'term_term_USST12', N'999_99999_09', N'')
    GO
    INSERT [dbo].[SampleData] ([PKO], [PKI], [Mapping]) VALUES (N'term_term_USST36', N'999_99999_09', N'')
    GO
    

    Thursday, June 13, 2019 12:30 PM
  • Hi Amir,

    Here is the query that I have come up with:

    WITH map
    AS (
    	SELECT *,
    		(
    			SELECT CASE count(*)
    					WHEN 0
    						THEN '1:0'
    					WHEN 1
    						THEN '1:1'
    					ELSE '1:M'
    					END
    			FROM SampleData
    			WHERE PKO = sd.PKO
    			) PKO_PKI,
    		(
    			SELECT CASE count(*)
    					WHEN 0
    						THEN '0:1'
    					WHEN 1
    						THEN '1:1'
    					ELSE 'M:1'
    					END
    			FROM SampleData
    			WHERE PKI = sd.PKI
    			) PKI_PKO
    	FROM SampleData sd
    	)
    SELECT PKO,
    	PKI,
    	PKO_PKI,
    	PKI_PKO,
    	CASE 
    		WHEN PKO_PKI = '1:M'
    			AND PKI_PKO = 'M:1'
    			THEN 'M:M'
    		ELSE NULL
    		END Many_to_Many
    FROM map;

    Result:

    There are three columns

    PKO_PKI: showing mapping from PKO to PKI

    PKI_PKO: showing mapping from PKI to PKO

    Many_to_Many: this is inferred looking at both PKO_PKI and PKI_PKO columns.

    I have highlighted the example that you presented.

    Hope this is what you are looking for. 

    Thanks,

    Lokesh Vij 

    Thursday, June 13, 2019 4:56 PM
  • Thanks Lokesh. I am afraid that is not returning the expected output.

    In the example SQL below, I have shown the expected row counts which I cannot reproduce from your table.

    -- 9 rows
    select s.PKO, count(s.PKI) PKI_Count
    from SampleData s
    group by s.PKO
    having count(s.PKI) > 1
    
    
    -- 13 rows
    select s.PKI, count(s.PKO) PKO_Count
    from SampleData s
    group by s.PKI
    having count(s.PKO) > 1
    
    

    Thursday, June 13, 2019 5:17 PM
  • Hi Amir,

    We both are looking at same data differently 8)

    Here is the modified version of the query filtered to show PKO_Count(PKI) records. Here M:N are actual ratio numbers

    WITH map
    AS (
    	SELECT *,
    		(
    			SELECT CASE count(*)
    					WHEN 0
    						THEN '1:0'
    					WHEN 1
    						THEN '1:1'
    					ELSE '1:'+cast(count(*) AS varchar(3))
    					END
    			FROM SampleData
    			WHERE PKO = sd.PKO
    			) PKO_PKI,
    		(
    			SELECT CASE count(*)
    					WHEN 0
    						THEN '0:1'
    					WHEN 1
    						THEN '1:1'
    					ELSE cast(count(*) AS varchar(3))+':1'
    					END
    			FROM SampleData
    			WHERE PKI = sd.PKI
    			) PKI_PKO
    	FROM SampleData sd
    	)
    ,final_map AS (
    SELECT PKO,
    	PKI,
    	PKO_PKI,
    	PKI_PKO,
    	CASE 
    		WHEN right(PKO_PKI,1) > 1
    			AND left(PKI_PKO,1) > 1
    			THEN left(PKI_PKO,1)+':'+right(PKO_PKI,1)
    		ELSE NULL
    		END Many_to_Many
    FROM map)
    SELECT *
    FROM final_map
    WHERE pko_pki <> '1:1'
    ORDER BY PKO;WITH map
    AS (
    	SELECT *,
    		(
    			SELECT CASE count(*)
    					WHEN 0
    						THEN '1:0'
    					WHEN 1
    						THEN '1:1'
    					ELSE '1:'+cast(count(*) AS varchar(3))
    					END
    			FROM SampleData
    			WHERE PKO = sd.PKO
    			) PKO_PKI,
    		(
    			SELECT CASE count(*)
    					WHEN 0
    						THEN '0:1'
    					WHEN 1
    						THEN '1:1'
    					ELSE cast(count(*) AS varchar(3))+':1'
    					END
    			FROM SampleData
    			WHERE PKI = sd.PKI
    			) PKI_PKO
    	FROM SampleData sd
    	)
    ,final_map AS (
    SELECT PKO,
    	PKI,
    	PKO_PKI,
    	PKI_PKO,
    	CASE 
    		WHEN right(PKO_PKI,1) > 1
    			AND left(PKI_PKO,1) > 1
    			THEN left(PKI_PKO,1)+':'+right(PKO_PKI,1)
    		ELSE NULL
    		END Many_to_Many
    FROM map)
    SELECT *
    FROM final_map
    WHERE pko_pki <> '1:1'
    ORDER BY PKO;

    Here is the mapping from your output to mine. Hope that makes sense!

    Thursday, June 13, 2019 5:52 PM
  • Thanks Lokesh. Now I get it.
    Friday, June 14, 2019 10:04 PM