I have two tables. One is the raw table and another one is the table that I need to reference from. I am not sure how to join this two tables because the structure is different. I am trying to create two new tables based on the reference table. Below is the sample data and expected output:

#Raw table

#Specnorm

Expected output

#Screener table

#Harvest table

CodePudding user response：

Try to build on this:

DROP TABLE IF EXISTS #Specnorm;

CREATE TABLE #Specnorm
(
    [tablename] SYSNAME
   ,[fieldname] SYSNAME
   ,[destfieldname] SYSNAME
   ,[sequence] BIGINT
);

INSERT INTO #Specnorm ([tablename], [fieldname], [destfieldname], [sequence])
VALUES ('Screener' ,'B_01', 'Area1' ,   1)
      ,('Screener' ,'B_02', 'Area2' ,   2)
      ,('Product'  ,'C_01', 'ProductID1',   3)
      ,('Product'  ,'C_02', 'ProductID2',   4);

DECLARE @source_data_table_name SYSNAME = '#Raw';
DECLARE @DynamicTSQLStatement NVARCHAR(MAX);

SELECT @DynamicTSQLStatement = STRING_AGG(CAST([table_definiton] AS NVARCHAR(MAX)), ';')
FROM
(
    SELECT 'SELECT '   STRING_AGG(CAST(QUOTENAME([fieldname])   ' AS '   QUOTENAME([destfieldname]) AS NVARCHAR(MAX)), ', ') WITHIN GROUP (ORDER BY [sequence])     ' INTO '   MAX([tablename])   ' FROM '   @source_data_table_name    
    FROM #Specnorm
    GROUP BY [tablename]
) DS ([table_definiton])

EXEC sp_executesql @DynamicTSQLStatement;