Sample Data:
DECLARE @Tbl_List TABLE
(
[PSchemaName] sysname
, [PTableName] sysname
, [PColumnName] sysname
, [FSchemaName] sysname
, [FTableName] sysname
, [FColumnName] sysname
, [ColumnOrder] TINYINT
) ;
INSERT INTO @Tbl_List
VALUES
( 'emp', 'emphdr1', 'id', 'emp', 'empdtl1', 'hdrid', 1 )
, ( 'emp', 'emphdr2', 'id', 'emp', 'empdtl2', 'hdrid', 1 )
, ( 'emp', 'emphdr2', 'key', 'emp', 'empdtl2', 'hdrkey', 2 )
, ( 'emp', 'emphdr3', 'id', 'emp', 'empdtl3', 'hdrid', 1 )
, ( 'emp', 'emphdr3', 'key1', 'emp', 'empdtl3', 'hdrkey1', 2 )
, ( 'emp', 'emphdr3', 'key2', 'emp', 'empdtl3', 'hdrkey2', 3 )
, ( 'emp', 'emphdr3', 'id', 'emp', 'empdtl4', 'hdrid', 1 )
, ( 'emp', 'emphdr3', 'key1', 'emp', 'empdtl4', 'hdrkey1', 2 ) ;
PSchemaName PTableName PColumnName FSchemaName FTableName FColumnName ColumnOrder
emp emphdr1 id emp empdtl1 hdrid 1
emp emphdr2 id emp empdtl2 hdrid 1
emp emphdr2 key emp empdtl2 hdrkey 2
emp emphdr3 id emp empdtl3 hdrid 1
emp emphdr3 key1 emp empdtl3 hdrkey1 2
emp emphdr3 key2 emp empdtl3 hdrkey2 3
emp emphdr3 id emp empdtl4 hdrid 1
emp emphdr3 key1 emp empdtl4 hdrkey1 2
Goal:
Dynamically create/output a SELECT statement - that JOINs FSchema/FTable with PSchema/PTable on (F/S)ColumnName in the order specified in ColumnOrder - in a new column.
query would be derived like.. "SELECT [F].[{FColumnName}], [P].[{PColumnName}] FROM [{FSchemaName}].[{FTableName}] AS [F] JOIN [{PSchemaName}].[{PTableName}] AS [P] ON [P].[{PColumnName}] = [F].[{FColumnName}] ;" (again, the ON clause is derived in the order specified in the [ColumnOrder] field)
Expected Output:
PSchemaName PTableName FSchemaName FTableName CMD
emp emphdr1 emp empdtl1 SELECT [F].[hdrid], [P].[id] FROM [emp].[empdtl1] AS [F] JOIN [emp].[emphdr1] AS [P] ON [P].[id] = [F].[hdrid] ;
emp emphdr2 emp empdtl2 SELECT [F].[hdrid], [F].[hdrkey], [P].[id], [P].[key] FROM [emp].[empdtl2] AS [F] JOIN [emp].[emphdr2] AS [P] ON [P].[id] = [F].[hdrid] AND [P].[key] = [F].[hdrkey] ;
emp emphdr3 emp empdtl3 SELECT [F].[hdrid], [F].[hdrkey1], [F].[hdrkey2], [P].[id], [P].[key1], [P].[key2] FROM [emp].[empdtl3] AS [F] JOIN [emp].[emphdr3] AS [P] ON [P].[id] = [F].[hdrid] AND [P].[key1] = [F].[hdrkey1] AND [P].[key2] = [F].[hdrkey2] ;
emp emphdr3 emp empdtl4 SELECT [F].[hdrid], [F].[hdrkey1], [P].[id], [P].[key1] FROM [emp].[empdtl3] AS [F] JOIN [emp].[emphdr4] AS [P] ON [P].[id] = [F].[hdrid] AND [P].[key1] = [F].[hdrkey1] ;
My attempt:
I'm still trying to figure out a way. Concatenating a single column can be achieved by FOR XML, not sure how I would go about concatenating "=" between two columns and adding "AND" for joins with multiple columns..
CodePudding user response:
Using Group By
and the String_Agg
function, you can generate the desired output.
SELECT PSchemaName,PTableName,FSchemaName,FTableName,
CONCAT('SELECT ',fselectcol,',',pselectcol,' FROM ',FTableName ,' f JOIN ',PTableName,' p ON ',joincol) AS CMD
FROM
(SELECT
MAX(PSchemaName) AS PSchemaName,
MAX(PTableName) AS PTableName,
MAX(FSchemaName) AS FSchemaName,
FTableName,
STRING_AGG('p.' PColumnName,',') pselectcol,
STRING_AGG('f.' FColumnName,',') fselectcol,
STRING_AGG('p.' PColumnName ' = f.' FColumnName,' AND ') joincol
FROM @Tbl_List
GROUP BY FTableName) t
EDIT: SQL-SERVER 2016
does not support String_Agg
, so use XML PATH
SELECT
MAX(PSchemaName) AS PSchemaName,
MAX(PTableName) AS PTableName,
MAX(FSchemaName) AS FSchemaName,
FTableName,
CONCAT('SELECT ',MAX(fselectcol),',',MAX(pselectcol),' FROM ',FTableName ,' f JOIN ',MAX(PTableName),' p ON ', SUBSTRING(MAX(joincol),0,LEN(MAX(joincol)) - 3)) AS CMD
FROM
(SELECT t1.*,
STUFF(
(SELECT ',p.' PColumnName
FROM @Tbl_List AS t2
WHERE t2.FTableName = t1.FTableName
FOR XML PATH('')), 1, 1, NULL) AS pselectcol,
STUFF(
(SELECT ',f.' FColumnName
FROM @Tbl_List AS t2
WHERE t2.FTableName = t1.FTableName
FOR XML PATH('')), 1, 1, NULL) AS fselectcol,
STUFF(
(SELECT 'p.' PColumnName ' = f.' FColumnName,' AND '
FROM @Tbl_List AS t2
WHERE t2.FTableName = t1.FTableName
FOR XML PATH('')), 1, 0, NULL) AS joincol
FROM @Tbl_List AS t1) T
GROUP BY FTableName
demo in db<>fiddle - sql server 2016
demo in db<>fiddle
CodePudding user response:
Using a mix of Cross Apply and For Xml to generate the queries.
SELECT
[PSchemaName], [PTableName], [FSchemaName], [FTableName]
, [Cmd] = CONCAT('SELECT ', [Fields], char(10),
'FROM ', [From], char(10),
'JOIN ', [Join], char(10), [On])
FROM
(
SELECT
[FSchemaName], [FTableName], [PSchemaName], [PTableName]
, [From] = CONCAT(QUOTENAME([FSchemaName]), '.', QUOTENAME([FTableName]), ' AS [F] ')
, [Join] = CONCAT(QUOTENAME([PSchemaName]), '.', QUOTENAME([PTableName]), ' AS [P] ')
FROM @Tbl_List
GROUP BY
[FSchemaName], [FTableName], [PSchemaName], [PTableName]
) t
CROSS APPLY (
SELECT
[Fields] = STUFF(
(SELECT ', [F].' QUOTENAME([FColumnName])
', [P].' QUOTENAME([PColumnName])
FROM @Tbl_List t2
WHERE t2.FTableName = t.FTableName
FOR XML PATH('')), 1, 2, NULL)
, [On] = ' ON' STUFF(
(SELECT ' AND [P].' QUOTENAME([PColumnName])
' = [F].' QUOTENAME([FColumnName]) char(10)
FROM @Tbl_List t2
WHERE t2.FTableName = t.FTableName
FOR XML PATH('')), 1, 4, NULL)
from @Tbl_List t2
where t2.FSchemaName = t.FSchemaName
and t2.FTableName = t.FTableName
) ca
PSchemaName | PTableName | FSchemaName | FTableName | Cmd |
---|---|---|---|---|
emp | emphdr1 | emp | empdtl1 | SELECT [F].[hdrid], [P].[id]<br>FROM [emp].[empdtl1] AS [F] <br>JOIN [emp].[emphdr1] AS [P] <br> ON [P].[id] = [F].[hdrid]<br> |
emp | emphdr2 | emp | empdtl2 | SELECT [F].[hdrid], [P].[id], [F].[hdrkey], [P].[key]<br>FROM [emp].[empdtl2] AS [F] <br>JOIN [emp].[emphdr2] AS [P] <br> ON [P].[id] = [F].[hdrid]<br> AND [P].[key] = [F].[hdrkey]<br> |
emp | emphdr2 | emp | empdtl2 | SELECT [F].[hdrid], [P].[id], [F].[hdrkey], [P].[key]<br>FROM [emp].[empdtl2] AS [F] <br>JOIN [emp].[emphdr2] AS [P] <br> ON [P].[id] = [F].[hdrid]<br> AND [P].[key] = [F].[hdrkey]<br> |
emp | emphdr3 | emp | empdtl3 | SELECT [F].[hdrid], [P].[id], [F].[hdrkey1], [P].[key1], [F].[hdrkey2], [P].[key2]<br>FROM [emp].[empdtl3] AS [F] <br>JOIN [emp].[emphdr3] AS [P] <br> ON [P].[id] = [F].[hdrid]<br> AND [P].[key1] = [F].[hdrkey1]<br> AND [P].[key2] = [F].[hdrkey2]<br> |
emp | emphdr3 | emp | empdtl3 | SELECT [F].[hdrid], [P].[id], [F].[hdrkey1], [P].[key1], [F].[hdrkey2], [P].[key2]<br>FROM [emp].[empdtl3] AS [F] <br>JOIN [emp].[emphdr3] AS [P] <br> ON [P].[id] = [F].[hdrid]<br> AND [P].[key1] = [F].[hdrkey1]<br> AND [P].[key2] = [F].[hdrkey2]<br> |
emp | emphdr3 | emp | empdtl3 | SELECT [F].[hdrid], [P].[id], [F].[hdrkey1], [P].[key1], [F].[hdrkey2], [P].[key2]<br>FROM [emp].[empdtl3] AS [F] <br>JOIN [emp].[emphdr3] AS [P] <br> ON [P].[id] = [F].[hdrid]<br> AND [P].[key1] = [F].[hdrkey1]<br> AND [P].[key2] = [F].[hdrkey2]<br> |
emp | emphdr3 | emp | empdtl4 | SELECT [F].[hdrid], [P].[id], [F].[hdrkey1], [P].[key1]<br>FROM [emp].[empdtl4] AS [F] <br>JOIN [emp].[emphdr3] AS [P] <br> ON [P].[id] = [F].[hdrid]<br> AND [P].[key1] = [F].[hdrkey1]<br> |
emp | emphdr3 | emp | empdtl4 | SELECT [F].[hdrid], [P].[id], [F].[hdrkey1], [P].[key1]<br>FROM [emp].[empdtl4] AS [F] <br>JOIN [emp].[emphdr3] AS [P] <br> ON [P].[id] = [F].[hdrid]<br> AND [P].[key1] = [F].[hdrkey1]<br> |
Demo on db<>fiddle here