none
Using Mapping information and form a sql string RRS feed

  • Question

  • Hi ,

    We have scenario where we are allowing the user to model a star schema relation on the object in a web interface and the data is stored in a table in following structure

    TableMap

    SourceTable TargetTable JoinCondition
    A B A.ID=B.ID
    A C A.ID=C.ID
    D E D.Age=E.Age
    C D C.EMPID=D.EMPID
     

    We are looking for query where we would like to get the output in form like a SQL Join String :

    Preferred Output as a single row single column: 

    A Join B on A.ID=B.ID Join C on A.ID=C.ID Join D on C.EMPID=D.EMPID Join E on D.Age=E.Age

    Sunday, September 8, 2019 4:56 PM

All replies

  • Hi,

    Can you provide DDL,DML scripts and expected o/p with example?

    Sunday, September 8, 2019 5:13 PM
  • create table tablejoin (sourcetable char(100),targettable char(100))

    insert into tablejoin select 'A','B','A.ID=B.ID';
    insert into tablejoin select 'A','C','A.ID=C.ID';
    insert into tablejoin select 'D','E','D.Age=E.Age';
    insert into tablejoin select 'C','D','C.EMPID=D.EMPID';

    Expected Join - SIngle Row Single Columns and the value of the column should be string with value as below: 

    'A Join B on A.ID=B.ID Join C on A.ID=C.ID Join D on C.EMPID=D.EMPID Join E on D.Age=E.Age'

    Sunday, September 8, 2019 5:41 PM
  • Hi James,

    Can you correct the script please.

    table named 'tablejoin' has 2 columns only.

    insert scripts has 3 values to pass.

    Sunday, September 8, 2019 5:49 PM
  • create table tablejoin (sourcetable char(100),targettable char(100),JoinCond char(100))
    Sunday, September 8, 2019 5:54 PM
  • Hi JamesIND,

    Hope the below result will help you!

    create table tablejoin (sourcetable char(100),targettable char(100),joincond char(100))
    
    insert into tablejoin select 'A','B','A.ID=B.ID';
    insert into tablejoin select 'A','C','A.ID=C.ID';
    insert into tablejoin select 'D','E','D.Age=E.Age';
    insert into tablejoin select 'C','D','C.EMPID=D.EMPID';
    
    --select * from tablejoin;
    
    CREate table #temp(names varchar(500));
    
    ;WITH CTE (ID,NAMES)
    AS
    (
    SELECT ROW_NUMBER() OVER ( ORDER BY sourcetable,targettable,joincond),
    RTRIM(LTRIM(SourceTable)) + ' JOIN ' + RTRIM(LTRIM(TargetTable)) + ' on ' + RTRIM(LTRIM(joincond))
    FROM   tablejoin
    )
    INSERT INTO #TEMP
    SELECT NAMES FROM CTE WHERE ID=1
    UNION ALL 
    SELECT SUBSTRING(NAMES,CHARINDEX('JOIN',NAMES),LEN(NAMES)) FROM CTE WHERE ID<>1;
    
    select STRING_AGG(names,char(10)) AS RESULTSET from #temp;
    
    --drop table tablejoin;
    drop table #temp;

    Note: Suggested one of the many ways that is easy to understand and implement.
    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks,
    Arulmouzhi

    Sunday, September 8, 2019 6:17 PM
  • Here are two solutions. The first runs on SQL 2017 only, the second runs on SQL 2005 and up.

    The solutions do not produce the result you were asking for but an equivalent string:

    A CROSS JOIN B CROSS JOIN C CROSS JOIN D CROSS JOIN E WHERE A.ID=B.ID AND A.ID=C.ID AND D.Age=E.Age AND C.EMPID=D.EMPID

    The reason I did this is because with the JOIN operator, it becomes complex since the conditions must come in the correct order. You cannot havr

    A JOIN B ON A.ID=B.ID JOIN D ON D.Age=E.Age JOIN E ...

    but this will yield an error that E is not define at this point.

    -- SQL 2017
    SELECT (SELECT string_agg(tbl, ' CROSS JOIN ')       
    FROM   (SELECT DISTINCT SourceTable AS tbl
                 FROM tablejoin                
                UNION                
                SELECT DISTINCT TargetTable FROM tablejoin)  u)
             + ' WHERE ' +
              (SELECT string_agg(JoinCondition, ' AND ')
              FROM   tablejoin)

    -- All versions
    DECLARE @result nvarchar(MAX)
    SELECT @result =        (SELECT tbl + ' CROSS JOIN '
                                        FROM   (SELECT DISTINCT SourceTable AS tbl
                                                    FROM tablejoin                
                                                 UNION    SELECT DISTINCT TargetTable FROM tablejoin) u        
                                       FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')

    SELECT @result = substring(@result, 1, len(@result) - len (' CROSS JOIN')) + ' WHERE ' +
           (SELECT JoinCondition + ' AND '
            FROM   tablejoin
            FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')
    SELECT @result = substring(@result, 1, len(@result) - len(' AND'))
    SELECT @result


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se


    Sunday, September 8, 2019 6:17 PM
  • Hi,

    Added method 2, you can also try this.

    create table tablejoin (sourcetable char(100),targettable char(100),joincond char(100))
    
    insert into tablejoin select 'A','B','A.ID=B.ID';
    insert into tablejoin select 'A','C','A.ID=C.ID';
    insert into tablejoin select 'D','E','D.Age=E.Age';
    insert into tablejoin select 'C','D','C.EMPID=D.EMPID';
    
    --select * from tablejoin;
    
    CREate table #temp(names varchar(500));
    
    ;WITH CTE (ID,NAMES)
    AS
    (
    SELECT ROW_NUMBER() OVER ( ORDER BY sourcetable,targettable,joincond),
    RTRIM(LTRIM(SourceTable)) + ' JOIN ' + RTRIM(LTRIM(TargetTable)) + ' on ' + RTRIM(LTRIM(joincond))
    FROM   tablejoin
    )
    INSERT INTO #TEMP
    SELECT NAMES FROM CTE WHERE ID=1
    UNION ALL 
    SELECT SUBSTRING(NAMES,CHARINDEX('JOIN',NAMES),LEN(NAMES)) FROM CTE WHERE ID<>1;
    
    --method 1
    select STRING_AGG(names,char(10)) AS RESULTSET from #temp;
    
    --method 2
    select 			distinct 	STUFF(CAST((
    					SELECT   '  ' +CAST(c.names AS VARCHAR(MAX))
    					FROM (  
    							SELECT distinct names
    							FROM   #temp
    						) c
    					FOR XML PATH(''), TYPE) AS VARCHAR(MAX)), 1, 2, '') AS results
    					from #temp t;
    
    drop table tablejoin;
    drop table #temp;

    Note: Suggested some of the many ways that are easy to understand and implement.
    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks,
    Arulmouzhi

    Sunday, September 8, 2019 6:27 PM
  • 

    Hi JamesIND,

     

    Please try following script .

     
    create table tablejoin (sourcetable char(100),targettable char(100),JoinCond char(100))
    
    insert into tablejoin select 'A','B','A.ID=B.ID';
    insert into tablejoin select 'A','C','A.ID=C.ID';
    insert into tablejoin select 'D','E','D.Age=E.Age';
    insert into tablejoin select 'C','D','C.EMPID=D.EMPID';
    
    ;with cte as(
    select *,row_number()over(order by sourcetable,targettable ) rn from tablejoin
    )
    select STRING_AGG(case when rn=1 
    then RTRIM(LTRIM(sourcetable))+' JOIN '+RTRIM(LTRIM(targettable))+' ON '+ RTRIM(LTRIM(JoinCond))
    else ' JOIN '+RTRIM(LTRIM(targettable))+' ON '+ RTRIM(LTRIM(JoinCond)) end ,' 
    ') result 
    from cte
    /*
    result
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    A JOIN B ON A.ID=B.ID 
     JOIN C ON A.ID=C.ID 
     JOIN D ON C.EMPID=D.EMPID 
     JOIN E ON D.Age=E.Age
    */
    

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

    Monday, September 9, 2019 3:04 AM
  • This would not meet the requirement. Consider the scenario below : 

    Insert into TableJoin Select 'A','Z','A->Z';

    Insert into TableJoin Select 'B','C','B->C';

    Insert into TableJoin Select 'D','F','D->F';

    Insert into TableJoin Select 'Z','B','Z->B';

    Insert into TableJoin Select 'F','C','F->C';

    The accepted out put would be 5 rows in the outputs

    SourceTable,TargetTable,JoinCond

    A,Z,A->Z

    Z,B,Z->B

    B,C,B->C

    F,C,F->C

    D,F,D->F

    Thanks in Advance for the help

    Monday, September 30, 2019 1:13 PM
  • This would not meet the requirement. Consider the scenario below : 

    Insert into TableJoin Select 'A','Z','A->Z';

    Insert into TableJoin Select 'B','C','B->C';

    Insert into TableJoin Select 'D','F','D->F';

    Insert into TableJoin Select 'Z','B','Z->B';

    Insert into TableJoin Select 'F','C','F->C';

    Now it says A->Z. Previousy you had A.ID=B.ID?

    It's not strange if the answers get do not match the requirements, if you change them.

    And what you are looking for now, I am not able to understand at all.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, September 30, 2019 9:47 PM
  • Apologise if the data has confused the scenario. What we are looking for is suppose we stored the relationship between the objects/table as shown in the data , using the mapping information we need build a sql query based on correct join order. 

    For example if the first row represents the Table A Is related to Table B and then the 2nd row had Table C Is related to Table D. We cannot simply take the row as is and try to form a Join Query String. We need to reorder the data in the correct dependency form and then form the SQL String

    Insert into TableJoin Select 'A','Z','A.ID=Z.ID';

    Insert into TableJoin Select 'B','C','B.ID=C.ID';

    Insert into TableJoin Select 'D','F','D.ID=F.ID';

    Insert into TableJoin Select 'Z','B','Z.ID=B.ID';

    Insert into TableJoin Select 'F','C','F.ID=C.ID';

    Expected Result : 

    SourceTable,TargetTable,JoinCond

    'A','Z','A.ID=Z.ID''Z','B','Z.ID=B.ID'

    'B','C','B.ID=C.ID''F','C','F.ID=C.ID'

    'D','F','D.ID=F.ID'

    Tuesday, October 1, 2019 4:32 AM
  • select string_agg(sourcetable,' join ') + ' on ' + string_agg(joincond,' on ') 
    from tablejoin
    Tuesday, October 1, 2019 6:15 AM
  • This would not give the resultset in the required order. This will just concact thw multiple rows in to single row of data. What we need is the the data to be rearranged or sorted out in correct order based on the table join order dependency
    Tuesday, October 1, 2019 10:33 AM
  • This would not give the resultset in the required order. This will just concact thw multiple rows in to single row of data. What we need is the the data to be rearranged or sorted out in correct order based on the table join order dependency
    To avoid the dependency problem I suggested a solution with CROSS JOIN already on Sep 8th. Since you never commented on it, I don't know why it did not meet your requirements.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, October 1, 2019 11:17 AM