SQL Server Dynamis SQL joins

yshpjwxd 于 2022-12-10 发布在其他

关注(0)|答案(1)|浏览(186)

I have a requirement where I will need to build dynamic sql relating to joins.
All the information (the table names and the joining column) for the join statement will be contained in a table.
While I can easily get the table names, I cannot figure out how to construct the "source tables" dynamic join...

The first table should be the 'FROM', but how do I determine the first table (there may be up to 5 tables)
The following tables should be the 'LEFT JOIN ON', but how do I dynamically build the THIS = THAT and THIS = THAT parts?
Using STUFF, this is as close I I have gotten to solving solution, which is not much.
OHS_MRI_Encounter_RUSH ACT ON ACT, OHS_MRI_Insurance_RUSH INS ON INS, OHS_MRI_Patient_Account_RUSH R02 ON R02
I can replace the comma's with 'LEFT JOIN' and CONCAT the 'ON', but how would I make the dynamic INS.COLUMN1 = ACT.COLUMN1? The ending result would be:

FROM OHS_MRI_Encounter_RUSH ACT ON ACT
LEFT JOIN OHS_MRI_Insurance_RUSH INS ON INS.COLUMN1 = ACT.COLUMN1 AND INS.COLUMN1 = 
R02.COLUMN1 
LEFT JOIN OHS_MRI_Patient_Account_RUSH R02 ON R02.COLUMN1 = ACT.COLUMN1 AND R02.COLUMN1 
= INS.COLUMN1

I tried implicit joins but this only works if all tables have relating rows, which, in my case, will not always be the case.
I must use dynamic SQL as the tables and columns will change.

sql-server

来源：https://stackoverflow.com/questions/74690911/dynamis-sql-joins

1条答案

按热度按时间

mqkwyuun1#

I did a solution, but imagining a detail that was not included in the question: where should the comparisons on the join criteria come from? For my answer, those comparisons must be on the commas' right.

DECLARE @source TABLE (dynamic_sql_info VARCHAR(200))

INSERT INTO @source
VALUES ('
OHS_MRI_Encounter_RUSH ACT ON ACT, 
OHS_MRI_Insurance_RUSH INS ON INS.this=ACT.that and INS.other > ACT.another, 
OHS_MRI_Patient_Account_RUSH R02 ON R02.this=INS.that')

;WITH split_data AS (
    SELECT row_number() OVER (ORDER BY (SELECT NULL)) AS ranking
        ,ss.table_name
        ,ss.condition
    FROM @source s
    OUTER APPLY (
        SELECT
        left(trimed.value, charindex(' ON ', trimed.value)) AS table_name
        ,right(trimed.value, len(trimed.value) - charindex(' ON ', trimed.value) - 3) AS condition
        FROM
        STRING_SPLIT(s.dynamic_sql_info, ',') split --Breaks it by commas
        CROSS APPLY (
            SELECT trim(split.value) value
        ) AS trimed
    ) AS ss
)
SELECT CASE sd.ranking
WHEN 1 THEN 'FROM '+sd.table_name
ELSE 'LEFT JOIN '+sd.table_name+' ON '+sd.condition
END AS table_instructions
FROM split_data sd

table_instructions
FROM OHS_MRI_Encounter_RUSH ACT
LEFT JOIN OHS_MRI_Insurance_RUSH INS ON INS.this=ACT.that and INS.other > ACT.another
LEFT JOIN OHS_MRI_Patient_Account_RUSH R02 ON R02.this=INS.that

If this doesn't work for your data, please let me know, and provide more details.
You can see this running on DB Fiddle .
Code for SQL Server older than 2016: DB Fiddle

赞(0）回复(0）举报 2022-12-10

我来回答

SQL Server Dynamis SQL joins

1条答案

相关问题

热门标签

最新问答