Joins dynamicall based on the column list available

Question

Dear All,

Could you please suggest a way to achieve a way to join two tables where join columns are dynamic in nature as below.

Table A: has two columns
Column_toLook, Column_value
India,100
India,101
India,102
UK,200
UK,201
US,300,
US,301

Table 2 : has around 6 countries of data (static columns here)
India,UK,US,Japan,Aus,germany
100,200,300,400,500,600
101,201,301,401,501,601
105,205,305,405,505,605

Join condition :
First we need to take the list of countries present in Table A --> Column_toLook data and based on the column name come to table B, match the column_values in this tableB based on matching country name, Look for the Column_value presence in TableB, if matches add 'TRUE' if not 'FALSE' as a flag column.

if we get more countries in table A tomorrow, like japan which is not present in the current data, we have to consider this dynamically and match the Japan column values in TABLE B and add a value in FLAG column.

Over all, we need to have another column as FALG with TRUE or FALSE based on dynamic column names coming from Table A.

--> I tried to convert the Table A Column_tolook data as Column names like Dynamic PIvot , but not able join it dynamically with table B. Because these data in COlumn A can vary daily.

Expected Output :
All the columns from Table B and an extra column as Flag with TRUE/FALSE in it.

Many thanks in advance.

Answer 1

   SELECT ...  
   FROM   tbl1 a  
   JOIN   tbl2 b ON a.ColumnValue = CASE a.ColumnToLookat  
                                       WHEN 'japan' THEN japan  
                                       WHEN 'india' THEN india  
                                       --etc  
                                    END  

Yes, that is not particularly appetising. But as Ronen points out, this is what happens when you make a incorrect design decision.

Answer 2

Hi,

In first glance this looks like a simple case of "CASE condition"

https://learn.microsoft.com/sql/t-sql/language-elements/case-transact-sql?view=sql-server-ver16&WT.mc_id=DP-MVP-5001699

Using CASE you can evaluates a list of conditions and returns one of columns from table 2 according to the condition. Obviously the condition in your case will be to check the name of the language in table 1.

Since your list of languages is limited to specific names (according to the columns in the second table) this should be simply done with a direct query. No dynamic queries are needed here probably.

Off-topic: Table 2 is not well designed and is limited to specific leagues. It might fit you current need but it is not flexible for future if you will need more languages. The different between a working design and a great design is that the first works today but the second take into consideration cases which you did not even think about today.

If you do not successes to implement this solution then please provide us the tools to reproduce your scenario. Please provide queries to create the tables and insert sample of data which represents all options

Answer 3

Hi @kirankumar-3620

Expected Output : All the columns from Table B and an extra column as Flag with TRUE/FALSE in it.

Sorry, I don't think the output you want is logical, if there's only one extra flag column.
Please check the following two kinds of outputs which might be more logical.

CREATE TABLE Table_A(  
Column_toLook VARCHAR(20), Column_value INT)  
INSERT INTO Table_A VALUES  
('India',100),  
('India',101),  
('India',102),  
('UK',200),  
('UK',201),  
('US',300),  
('US',301);  
  
CREATE TABLE Table_B(India INT,UK INT,US INT,Japan INT,Aus INT,germany INT)  
INSERT INTO Table_B VALUES  
(100,200,300,400,500,600),  
(101,201,301,401,501,601),  
(105,205,305,405,505,605);  
  
--Output 1  
;WITH CTE AS  
(  
 SELECT *   
 FROM Table_B  
 UNPIVOT(Country_Value FOR Countries IN ([India],[UK],[US],[Japan],[Aus],[germany]))U  
)  
SELECT A.Column_toLook,A.Column_value,  
       CASE WHEN C.Country_Value IS NOT NULL THEN 'TRUE' ELSE 'FALSE' END AS FLAG  
FROM CTE C RIGHT JOIN Table_A A ON C.Countries= A.Column_toLook AND C.Country_Value=A.Column_value;  
  
--Output 2                               
;WITH CTE1 AS  
(  
 SELECT *   
 FROM Table_B  
 UNPIVOT(Country_Value FOR Countries IN ([India],[UK],[US],[Japan],[Aus],[germany]))U  
),CTE2 AS  
(  
 SELECT C1.Countries,C1.Country_Value  
       ,CASE WHEN A.Column_value IS NOT NULL THEN 'TRUE' ELSE 'FALSE' END AS FLAG  
       ,ROW_NUMBER()OVER(PARTITION BY Countries ORDER BY Country_Value) AS RNum  
 FROM CTE1 C1 LEFT JOIN Table_A A ON C1.Countries= A.Column_toLook AND C1.Country_Value=A.Column_value  
)  
SELECT [India_Value],[India_Flag],[UK_Value],[UK_Flag],[US_Value],[US_Flag]  
      ,[Japan_Value],[Japan_Flag],[Aus_Value],[Aus_Flag],[germany_Value],[germany_Flag]   
FROM (SELECT Countries+'_'+COLUMN_NAMES AS NEW_COLUMN,[VALUES],RNUM  
      FROM CTE2 CROSS APPLY(VALUES(CAST(Country_Value AS VARCHAR(20)),'Value'),(FLAG,'Flag'))C([VALUES],COLUMN_NAMES)  
     )S  
PIVOT(MAX([VALUES]) FOR NEW_COLUMN IN([India_Value],[India_Flag],[UK_Value],[UK_Flag],[US_Value],[US_Flag]  
                                     ,[Japan_Value],[Japan_Flag],[Aus_Value],[Aus_Flag],[germany_Value],[germany_Flag]))P  

If both of them don't meet your requirements, please provide the output you really want (you can clearly show it through the screenshot of excel).

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.