SQL Create dynamic column

Question

I am seeking some help to build an SQL to obtain certain results. Below are two tables.

Table 1

Col1     |        Col2 |    Col3 |  Col4
----------------------------------------    
TESTPC01 |  14/08/2014 |    ABCD |  CXYZ    
TESTPC02 |  14/08/2014 |    EFGH |  IJKL

Table2

COl1     |        Col2 |   Col3 |   Col4    
----------------------------------------
TESTPC01 |  14/08/2014 |    ENT |   DOC

RESULT

COl1     |        Col2 |   Col3 |  Col4  |  Col5 |  Col6
-------------------------------------------------------
TESTPC01 |  14/08/2014 |    ENT |   DOC  |  ABCD |  YES |
TESTPC02 |  14/08/2014 |   EFGH |   NULL |  NULL |   NO |

Yes only when there is data for that particular Col3 value is populated in the Table 2 for that specific matching Table1.Col1 = Table2.Col1.
If above condition fails then NO

I could build the SQL using simple select statements but no able to achieve to create the col6 dynamically. Can any one please guide me how to achieve this?

Yes, I did try that. But how to obtain this using case statement for specific value of matching values? I couldn't get my head around this. — msbikk

Gordon Linoff Gordon Linoff · Accepted Answer · 2014-08-14T10:52:19

You want a left outer join with a conditional expression:

select t2.col1, t2.col2, t2.col3,
       (case when t1.col1 is not null then t2.col4 end) as col4
       t1.col3 as col5,
       (case when t1.col1 is null then 'Yes' else 'No' end) as col6
from table2 t2 left join
     table1 t1
     on t2.col1 = t1.col1 and t2.col2 = t1.col1;

It is unclear to me why col4 would be NULL when there is no match. Usually, you would keep all values from the first table. However, that is how you have specified the results.

SQL Create dynamic column

1 Answers