none
trying to understand a predicate offered in another post RRS feed

Answers

  • I am glad that you asked!

    Translation Key (int pk)    System 1    Key 1    System 2    Key 2    System 3     Key 3 
    
    --------------------------    -----------    ------    ----------     ------    ----------      ------ 
    
    1                                    A                100       B               1000     C                 abc

    Your translation table (above) you wanted to UnPivot two elements "System" and "Key". To make sure "System1" shows the correct data along side "Key1" and "System2" shows correct data with "Key2" and so on... this join condition is used.

    what I am doing is, to extract the right 1 bit from the string. RIGHT('System1',1) will extract "1", RIGHT('System2',1) will extract "2", similarly, RIGHT('Key1',1) will extract "1", RIGHT('Key2',1) will extract "2"

    example, if the intermediate data looks like this:

    SystemX   KeyX   RIFHT(SystemX,1) RIFHT(KeyX,1)
    --------- ------ ---------------- -------------
    System1   Key1   1                1
    System1   Key2   1                2
    System1   Key3   1                3
    System2   Key1   2                1
    System2   Key2   2                2
    System2   Key3   2                3
    System3   Key1   3                1
    System3   Key2   3                2
    System3   Key3   3                3
    Filter predicate {WHERE right([systemx],1)  =right([keyx],1);}, will choose these three rows

    SystemX   KeyX   RIFHT(SystemX,1) RIFHT(KeyX,1)
    --------- ------ ---------------- -------------
    System1   Key1   1                1
    System2   Key2   2                2
    System3   Key3   3                3

    Hope you are able to follow the explanation!

    • Marked as answer by db042190 Tuesday, April 16, 2019 2:59 PM
    Monday, April 15, 2019 4:03 PM
  • Hi db042190,

     

    Thank you for your posting.

     

    Please see following script without WHERE clause. In the result , please check the value of the column 'systemx' and 'keyx'. And you will find that system1 matches key1,key2,key3 and system2,system3 also like that. So we add a condition to make system and key correspond one to one and then choose the rows we want .

     
    If Object_ID('Translation_Table','U') Is Not Null Drop Table Translation_Table
    go
    CREATE TABLE Translation_Table (
    	trans_key INT PRIMARY KEY,
    	system1 VARCHAR(10),
    	key1 VARCHAR(10),
    	system2 VARCHAR(10),
    	key2 VARCHAR(10),
    	system3 VARCHAR(10),
    	key3 VARCHAR(10)
    	);
    
    INSERT INTO Translation_Table
    VALUES (1,'A','100','B','1000','C','abc');
    
    
    
    SELECT *,right(systemx,1) [right(systemx,1)],right(keyx,1) [right(keyx,1)]
    FROM (
    	SELECT [trans_key],[system1],[key1],[system2],[key2],[system3],[key3]
    	FROM Translation_Table
    	) src
    unpivot ([system] for [systemx] in ([system1],[system2],[system3])) pvt  
    unpivot ([key] for [keyx] in ([key1],[key2],[key3])) pvt 
    ----WHERE right ([systemx],1)  = right ([keyx],1) ;
    /*
    trans_key   system     systemx       key        keyx     right(systemx,1) right(keyx,1)
    ----------- ---------- ------------- ---------- -------- ---------------- -------------
    1           A          system1       100        key1     1                1
    1           A          system1       1000       key2     1                2
    1           A          system1       abc        key3     1                3
    1           B          system2       100        key1     2                1
    1           B          system2       1000       key2     2                2
    1           B          system2       abc        key3     2                3
    1           C          system3       100        key1     3                1
    1           C          system3       1000       key2     3                2
    1           C          system3       abc        key3     3                3
    */
    

    Hope it can 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.

    • Marked as answer by db042190 Tuesday, April 16, 2019 2:59 PM
    Tuesday, April 16, 2019 2:43 AM

All replies

  • I am glad that you asked!

    Translation Key (int pk)    System 1    Key 1    System 2    Key 2    System 3     Key 3 
    
    --------------------------    -----------    ------    ----------     ------    ----------      ------ 
    
    1                                    A                100       B               1000     C                 abc

    Your translation table (above) you wanted to UnPivot two elements "System" and "Key". To make sure "System1" shows the correct data along side "Key1" and "System2" shows correct data with "Key2" and so on... this join condition is used.

    what I am doing is, to extract the right 1 bit from the string. RIGHT('System1',1) will extract "1", RIGHT('System2',1) will extract "2", similarly, RIGHT('Key1',1) will extract "1", RIGHT('Key2',1) will extract "2"

    example, if the intermediate data looks like this:

    SystemX   KeyX   RIFHT(SystemX,1) RIFHT(KeyX,1)
    --------- ------ ---------------- -------------
    System1   Key1   1                1
    System1   Key2   1                2
    System1   Key3   1                3
    System2   Key1   2                1
    System2   Key2   2                2
    System2   Key3   2                3
    System3   Key1   3                1
    System3   Key2   3                2
    System3   Key3   3                3
    Filter predicate {WHERE right([systemx],1)  =right([keyx],1);}, will choose these three rows

    SystemX   KeyX   RIFHT(SystemX,1) RIFHT(KeyX,1)
    --------- ------ ---------------- -------------
    System1   Key1   1                1
    System2   Key2   2                2
    System3   Key3   3                3

    Hope you are able to follow the explanation!

    • Marked as answer by db042190 Tuesday, April 16, 2019 2:59 PM
    Monday, April 15, 2019 4:03 PM
  • Hi db042190,

     

    Thank you for your posting.

     

    Please see following script without WHERE clause. In the result , please check the value of the column 'systemx' and 'keyx'. And you will find that system1 matches key1,key2,key3 and system2,system3 also like that. So we add a condition to make system and key correspond one to one and then choose the rows we want .

     
    If Object_ID('Translation_Table','U') Is Not Null Drop Table Translation_Table
    go
    CREATE TABLE Translation_Table (
    	trans_key INT PRIMARY KEY,
    	system1 VARCHAR(10),
    	key1 VARCHAR(10),
    	system2 VARCHAR(10),
    	key2 VARCHAR(10),
    	system3 VARCHAR(10),
    	key3 VARCHAR(10)
    	);
    
    INSERT INTO Translation_Table
    VALUES (1,'A','100','B','1000','C','abc');
    
    
    
    SELECT *,right(systemx,1) [right(systemx,1)],right(keyx,1) [right(keyx,1)]
    FROM (
    	SELECT [trans_key],[system1],[key1],[system2],[key2],[system3],[key3]
    	FROM Translation_Table
    	) src
    unpivot ([system] for [systemx] in ([system1],[system2],[system3])) pvt  
    unpivot ([key] for [keyx] in ([key1],[key2],[key3])) pvt 
    ----WHERE right ([systemx],1)  = right ([keyx],1) ;
    /*
    trans_key   system     systemx       key        keyx     right(systemx,1) right(keyx,1)
    ----------- ---------- ------------- ---------- -------- ---------------- -------------
    1           A          system1       100        key1     1                1
    1           A          system1       1000       key2     1                2
    1           A          system1       abc        key3     1                3
    1           B          system2       100        key1     2                1
    1           B          system2       1000       key2     2                2
    1           B          system2       abc        key3     2                3
    1           C          system3       100        key1     3                1
    1           C          system3       1000       key2     3                2
    1           C          system3       abc        key3     3                3
    */
    

    Hope it can 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.

    • Marked as answer by db042190 Tuesday, April 16, 2019 2:59 PM
    Tuesday, April 16, 2019 2:43 AM
  • thx, Rachel and Lokesh.  To minimize the confusion I've responded to Lokesh at the other post and i'm contemplating deleting this one.
    Tuesday, April 16, 2019 1:26 PM