none
has anyone seen a metadata based unpivot dynamic sql generator? RRS feed

  • Question

  • Hi we run 2016 enterprise.

    we have a number of entity key translation tables to help support our environment where various apps are purchased and we need to know that in purchased system A,  Joe's employee id is 100 but in purchased system B its 1000 etc etc.

    Here is an example...

    Translation table 1 has these cols and values for Joe,  

    Translation Key (int pk)    System 1    Key 1    System 2    Key 2    System 3     Key 3 

    --------------------------    -----------    ------    ----------     ------    ----------      ------ 

    1                                    A                100       B               1000     C                 abc

    Has anyone seen a metadata based approach to generating dynamic sql that would un pivot this table?  In other words there would be a table somewhere with the schema name and table name (view possible too) and column names, and appropriate key col name for this table and after reading that metadata would generate an un  pivot so the pk , a system and  a key would come out vertical, ie unpivoted?

    Friday, March 22, 2019 4:17 PM

Answers

  • thx will, the question isn't how to design the metadata.  in fact it can probably be derived from dmv's.  The question is for an example of how to dynamically generate an un pivot from such metadata. 

    It is possible to generate, here is an example with your data.

    DROP TABLE Translation_Table
    
    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');
    GO
    
    CREATE OR ALTER FUNCTION dbo.fn_unpivot_col_list 
    (
    	@table_name VARCHAR(100),
    	@pivot_column VARCHAR(100)
    )
    RETURNS VARCHAR(1000)
    AS
    BEGIN
    	DECLARE @col_list VARCHAR(max);
    
    	SELECT @col_list = coalesce(@col_list + ',' + quotename(column_name), quotename(column_name))
    	FROM INFORMATION_SCHEMA.columns
    	WHERE table_name = @table_name
    		AND reverse(SUBSTRING(REVERSE(column_name), PATINDEX('%[A-Z]%', REVERSE(column_name)), len(column_name))) = @pivot_column;
    
    	RETURN @col_list;
    END;
    GO
    /* table variable for pivot columns*/
    DECLARE @pivot_columns TABLE (
    	column_name VARCHAR(100),
    	column_list VARCHAR(max)
    	);
    
    INSERT INTO @pivot_columns (column_name)
    VALUES ('system'),
    	('key');
    
    DECLARE @table_name SYSNAME = 'Translation_Table',
    	    @col_list VARCHAR(max),
    	    @unpivot_select_list VARCHAR(max),
    	    @unpivot_str VARCHAR(max),
    	    @where_clause VARCHAR(max),
    	    @sql_text VARCHAR(max);
    
    UPDATE @pivot_columns
    SET column_list = dbo.fn_unpivot_col_list(@table_name, column_name);
    
    SELECT @unpivot_str = coalesce(@unpivot_str + column_list, column_list),
    	@where_clause = coalesce(@where_clause + ' = ' + where_clause, where_clause)
    FROM (
    	SELECT ' unpivot (' + QUOTENAME(column_name) + ' for ' + QUOTENAME(column_name + 'x') + ' in (' + column_list + ')) pvt ' column_list,
    		'right (' + QUOTENAME(column_name + 'x') + ',1) ' where_clause
    	FROM @pivot_columns
    	) x;
    
    SELECT @unpivot_select_list = coalesce(@unpivot_select_list + ',' + quotename(column_name), quotename(column_name))
    FROM (
    	SELECT column_name
    	FROM INFORMATION_SCHEMA.COLUMNS c
    	WHERE TABLE_NAME = @table_name
    		AND reverse(SUBSTRING(REVERSE(column_name), PATINDEX('%[A-Z]%', REVERSE(column_name)), len(column_name))) NOT IN (
    			SELECT column_name
    			FROM @pivot_columns
    			)
    	
    	UNION ALL
    	
    	SELECT column_name
    	FROM @pivot_columns
    	) x;
    
    SELECT @col_list = coalesce(@col_list + ',' + quotename(column_name), quotename(column_name))
    FROM INFORMATION_SCHEMA.COLUMNS c
    WHERE TABLE_NAME = @table_name;
    
    SELECT @unpivot_select_list,
    	@col_list,
    	@unpivot_str;
    
    SET @sql_text = 'SELECT ' + @unpivot_select_list + '
    FROM (
    	SELECT ' + @col_list + '
    	FROM ' + @table_name + '
    	) src
    ' + @unpivot_str + '
    WHERE ' + @where_clause + ';'
    
    PRINT @sql_text
    
    EXEC (@sql_text);


    It generates and executes this query:

    SELECT [trans_key],[system],[key]
    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) ;

    Output:

    trans_key   system     key
    ----------- ---------- ----------
    1           A          100
    1           B          1000
    1           C          abc



    • Edited by Lokesh Vij Wednesday, April 10, 2019 3:58 PM
    • Marked as answer by db042190 Thursday, April 11, 2019 11:23 AM
    Wednesday, April 10, 2019 3:57 PM
  • Yes, you got it correct, I mean to say the last character of the string (precisely) :)

    If you know the System and Key mapping before hand (or this is stored somewhere in the database), we can convert below predicate

    WHERE right ([systemx],1)  = right ([keyx],1)
    to this

    WHERE 
    (systemx = 'system1' and keyx = 'key1')
    OR (systemx = 'system2' and keyx = 'key2')
    OR (systemx = 'system3' and keyx = 'key3')

    • Marked as answer by db042190 Tuesday, April 16, 2019 2:58 PM
    Tuesday, April 16, 2019 2:01 PM

All replies

  • Hi db042190,

    How to generate metadata depends on design rules and business rules. I feel a little confused why you need to ask someone who is familiar with this. But I know that your intention is to get some ideas from someone who has experienced this.

    To be honest, this is not technical problem, it belongs to design category based on business rules and data model.

    And the opportunity of getting such an answer is very little here.

    Hope you can get it.

    Best Regards,

    Will


    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.

    Monday, March 25, 2019 7:45 AM
    Moderator
  • thx will, the question isn't how to design the metadata.  in fact it can probably be derived from dmv's.  The question is for an example of how to dynamically generate an un pivot from such metadata. 
    Wednesday, April 10, 2019 1:28 PM
  • thx will, the question isn't how to design the metadata.  in fact it can probably be derived from dmv's.  The question is for an example of how to dynamically generate an un pivot from such metadata. 

    It is possible to generate, here is an example with your data.

    DROP TABLE Translation_Table
    
    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');
    GO
    
    CREATE OR ALTER FUNCTION dbo.fn_unpivot_col_list 
    (
    	@table_name VARCHAR(100),
    	@pivot_column VARCHAR(100)
    )
    RETURNS VARCHAR(1000)
    AS
    BEGIN
    	DECLARE @col_list VARCHAR(max);
    
    	SELECT @col_list = coalesce(@col_list + ',' + quotename(column_name), quotename(column_name))
    	FROM INFORMATION_SCHEMA.columns
    	WHERE table_name = @table_name
    		AND reverse(SUBSTRING(REVERSE(column_name), PATINDEX('%[A-Z]%', REVERSE(column_name)), len(column_name))) = @pivot_column;
    
    	RETURN @col_list;
    END;
    GO
    /* table variable for pivot columns*/
    DECLARE @pivot_columns TABLE (
    	column_name VARCHAR(100),
    	column_list VARCHAR(max)
    	);
    
    INSERT INTO @pivot_columns (column_name)
    VALUES ('system'),
    	('key');
    
    DECLARE @table_name SYSNAME = 'Translation_Table',
    	    @col_list VARCHAR(max),
    	    @unpivot_select_list VARCHAR(max),
    	    @unpivot_str VARCHAR(max),
    	    @where_clause VARCHAR(max),
    	    @sql_text VARCHAR(max);
    
    UPDATE @pivot_columns
    SET column_list = dbo.fn_unpivot_col_list(@table_name, column_name);
    
    SELECT @unpivot_str = coalesce(@unpivot_str + column_list, column_list),
    	@where_clause = coalesce(@where_clause + ' = ' + where_clause, where_clause)
    FROM (
    	SELECT ' unpivot (' + QUOTENAME(column_name) + ' for ' + QUOTENAME(column_name + 'x') + ' in (' + column_list + ')) pvt ' column_list,
    		'right (' + QUOTENAME(column_name + 'x') + ',1) ' where_clause
    	FROM @pivot_columns
    	) x;
    
    SELECT @unpivot_select_list = coalesce(@unpivot_select_list + ',' + quotename(column_name), quotename(column_name))
    FROM (
    	SELECT column_name
    	FROM INFORMATION_SCHEMA.COLUMNS c
    	WHERE TABLE_NAME = @table_name
    		AND reverse(SUBSTRING(REVERSE(column_name), PATINDEX('%[A-Z]%', REVERSE(column_name)), len(column_name))) NOT IN (
    			SELECT column_name
    			FROM @pivot_columns
    			)
    	
    	UNION ALL
    	
    	SELECT column_name
    	FROM @pivot_columns
    	) x;
    
    SELECT @col_list = coalesce(@col_list + ',' + quotename(column_name), quotename(column_name))
    FROM INFORMATION_SCHEMA.COLUMNS c
    WHERE TABLE_NAME = @table_name;
    
    SELECT @unpivot_select_list,
    	@col_list,
    	@unpivot_str;
    
    SET @sql_text = 'SELECT ' + @unpivot_select_list + '
    FROM (
    	SELECT ' + @col_list + '
    	FROM ' + @table_name + '
    	) src
    ' + @unpivot_str + '
    WHERE ' + @where_clause + ';'
    
    PRINT @sql_text
    
    EXEC (@sql_text);


    It generates and executes this query:

    SELECT [trans_key],[system],[key]
    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) ;

    Output:

    trans_key   system     key
    ----------- ---------- ----------
    1           A          100
    1           B          1000
    1           C          abc



    • Edited by Lokesh Vij Wednesday, April 10, 2019 3:58 PM
    • Marked as answer by db042190 Thursday, April 11, 2019 11:23 AM
    Wednesday, April 10, 2019 3:57 PM
  • Hi Lokesh, what is this statement doing?  I think I know what the right function does but I'm not understanding how it is making a list of what appears to be all permutation of system x key work.

    WHERE right([systemx],1)  =right([keyx],1);


    • Edited by db042190 Friday, April 12, 2019 2:13 PM spelling
    Friday, April 12, 2019 2:13 PM
  • 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!

    Monday, April 15, 2019 4:03 PM
  • thx Lokesh you probably meant byte, not bit.

    glad I asked cuz system 1 isn't really called system 1.  nor is key 1 called key 1.   Actually systems are generally 3 byte acronyms.  so I think in order to use your solution (which I really like),  I'll need an approach where a system's name is appended to the name of its associated key and the length of that name will determine how many "right" bytes are compared.  Thoughts? 

    Tuesday, April 16, 2019 1:23 PM
  • Yes, you got it correct, I mean to say the last character of the string (precisely) :)

    If you know the System and Key mapping before hand (or this is stored somewhere in the database), we can convert below predicate

    WHERE right ([systemx],1)  = right ([keyx],1)
    to this

    WHERE 
    (systemx = 'system1' and keyx = 'key1')
    OR (systemx = 'system2' and keyx = 'key2')
    OR (systemx = 'system3' and keyx = 'key3')

    • Marked as answer by db042190 Tuesday, April 16, 2019 2:58 PM
    Tuesday, April 16, 2019 2:01 PM