Sql Querry for two scenario

1

Hi All,
I have two tables like below and tablename is the common field in both the tables.
Table1 contains all the columns names and second table contains only few columns by saying whether it is primary or secondary column.
I want to display all the columns data in below output format by showing all the columns belongs to primary and secondary.
table2 may not have any data for the data present in table1 in that case all the columns data present in table1 should display like no.
In some cases few columns belongs to both primary and secondary in both the tables.Pleae check below for output.Any one can guide/help me
Output:
TableName ColumnName Primary Secondary
Employee EID Yes No
Employee Name No No
Employee Dept No Yes
Employee Address No No
Country CID Yes No
Country CNAME No No
Country CLOCA No No
State SID No Yes
State SNAME No Yes
PINCODE PID No No
PINCODE PNAME No No
PINCODE PDESC No No
Test id Yes Yes
Test code Yes Yes
Test dtes No No

scripts for reference:
-----------------Table1---------------------

insert into table1 VALUES ('Employee','EID');
insert into table1 VALUES ('Employee','Name');
insert into table1 VALUES ('Employee','Dept');
insert into table1 VALUES ('Employee','Address');
insert into table1 VALUES ('Country','CID');
insert into table1 VALUES ('Country','CNAME');
insert into table1 VALUES ('Country','CLOCA');
insert into table1 VALUES ('State','SID');
insert into table1 VALUES ('State','SNAME');
insert into table1 VALUES ('PINCODE','PID');
insert into table1 VALUES ('PINCODE','PNAME');
insert into table1 VALUES ('PINCODE','PDESC');
insert into table1 VALUES ('Test','id');
insert into table1 VALUES ('Test','code');
insert into table1 VALUES ('Test','dtes');
----------------Table2-------------------------
insert into table2 VALUES ('Employee','EID','Primary');

insert into table2 VALUES ('Employee','Dept','Secondary');

insert into table2 VALUES ('Country','CID','Primary');

insert into table2 VALUES ('State','SID','Secondary');

insert into table2 VALUES ('Test','id','Primary');

insert into table2 VALUES ('Test','code','Primary');

insert into table2 VALUES ('Test','id','Secondary');

insert into table2 VALUES ('Test','code','Secondary');

==============Table scripts

CREATE TABLE table1
(
TABLENAME varchar(255),
COLUMNNAME varchar(255)

);
------Table2
CREATE TABLE table2
(
TABLENAME varchar(255),
COLUMNNAME varchar(255),
Relation varchar(255),

);

2

Try this:

select a.tablename
      ,a.columnname
      ,max(case when isnull(b.relation,'')='Primary'   then 'Yes' else 'No' end) as [Primary]
      ,max(case when isnull(b.relation,'')='Secondary' then 'Yes' else 'No' end) as Secondary
  from table1 as a
       left outer join table2 as b
                    on b.tablename=a.tablename
                   and b.columnname=a.columnname
 group by a.tablename
         ,a.columnname
;
1 Like
3

Thanks Bitsmed,Your solution works perfectly.