Need two rows after join from single row based on two columns

Question

I have a table1 with three columns and a table2 with single column.

If the value of first column is Y then I need a particular value from table 2 as a row in another table after join and if the second column is Y then I need a particular value from table 2 as another row in 3rd table after join. There is no common column in both the tables.

If two columns are in a row have Y as value then I need two rows in the final table after join. I'm using case right now for joining, but only one column is getting checked.

Can someone help me with this?

       table1                       
--------------------         
col1   col2  col3(pk)             
--------------------         
y       n     123                  
y       y     456


   table2                     
--------------------         
    col1               
--------------------         
    col1Y
    col2Y

Expected output

   table1                      
--------------------         
col1   col2             
--------------------         
 123   col1Y
 456   col1Y
 456   col2Y

Edit your question and provide (1) sample data; (2) desired results; (3) a tag for the database you are using. — Gordon Linoff, Aug 15 '18 at 12:09
I can't figure out how you get that expected result... Explain! — jarlh, Aug 15 '18 at 12:45

score 0 · Answer 1 · answered Aug 15 '18 at 13:40

0

select col3 as col1, 'col1y' as col2 from myTable where col1 = 'y'
union
select col3 as col1, 'col2y' as col2 from myTable where col2 = 'y'
--order by col1, col2;

SQLFiddle sample

answered Aug 15 '18 at 13:40

Cetin Basoz

22,495
3
31
39

score 0 · Answer 2 · answered Aug 15 '18 at 13:47

0

you also can check how to transpose tables with pivot command

SQL transpose full table

answered Aug 15 '18 at 13:47

Serkan Ekşioğlu

231
4
14

Damien_The_Unbeliever · Answer 3 · 2018-08-15T14:09:22.133

We can unpivot and join to get the results you're looking for:

declare @table1 table (col1 char(1),col2 char(1),col3 int)
insert into @table1(col1,col2,col3) values
('y','n',123)        ,   
('y','y',456)
declare @table2 table (col1 char(5))
insert into @table2 (col1) values
('col1Y'),('col2Y')

select
    u.col3 as col2,t2.col1 as col2
from
    @table1 t1
        unpivot
    (cval for cname in (col1,col2)) u
        cross apply
    (select cname + cval as Complete) v
        inner join
    @table2 t2
        on
            v.complete = t2.col1

Result:

col1        col2
----------- -----
123         col1Y
456         col1Y
456         col2Y

But after the unpivot and cross apply, we didn't really need table2 at all (we could have just filtered down to rows where cval is Y). But for now I've included it in case I'm missing something or there's more to build up in the query.

score 0 · Answer 4 · answered Aug 15 '18 at 15:48

Not sure if you need table2 but here is where you could do it with case statement.

SELECT col1, col2 from (
  SELECT 
CASE col1
  WHEN 'y' THEN col3
  ELSE 'null'
END AS col1,
CASE col1
  WHEN 'y' THEN 'col1Y'
  ELSE 'null'
END AS col2
from table1 as tbl1

union all

select
CASE col2
  WHEN 'y' THEN col3
  ELSE 'null'
END AS col1,
CASE col2
  WHEN 'y' THEN 'col2Y'
  ELSE 'null'
END AS col2
FROM table1 as tbl2) as tbl
where tbl.col1 <> 'null';

SQL Fiddle Sample

Need two rows after join from single row based on two columns

4 Answers4