CODEWITHSUNDEEP
sqloracle-database
Kᴀτᴢ
Kᴀτᴢ

Reputation: 2176

combine two tables with sql

I have those given tables:

enter image description here

In table2 could be multiple rows for "Kost_ID", in output these "Kost_ID" should be only one row. "MW_ID" should be in result "MW"+ number (here "MW5" and "MW6" for "MW_ID" 5+6). Hope this is clear enough.

How could I do this with sql in an oracle db? thanks

Table1 

ID  Kost_ID Col1
2016    1   bla
2016    2   bla
2016    3   bla1
2016    4   abl
2016    5   
2016    6   
2016    7   
2017    2   
2017    3   

Table2          

ID  Kost_ID MW_ID   Euro
2016    1   1   10
2016    2   2   20
2016    3   6   30
2016    3   5   40
2016    5   5   50
2016    6   6   60
2016    7   3   70
2016    4   4   80

Result:                             

ID  Kost_ID Col1    MW1 MW2 MW3 MW4 MW5 MW6
2016    1   bla 10                  
2016    2   bla     20              
2016    3   bla1                    40  30
2016    4   abl             80      
2016    5                       50  
2016    6                           60
2016    7               70

Upvotes: 1

Views: 94

Answers (3)

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

For completness pivot solution mentioned in comments:

select * 
  from table1 join table2 using (id, kost_id)
  pivot (sum(euro) for mw_id in (1 mw1, 2 mw2, 3 mw3, 4 mw4, 5 mw5, 6 mw6))
  order by id

Test example

(sorry for the delay)

Upvotes: 1

Javlon Ismatov
Javlon Ismatov

Reputation: 194

i came close but it needs some improvement.

select  a.ID, a.Kost_ID,a.Col1,
case b.MW_ID when 1 then b.Euro else null end MW1,
case b.MW_ID when 2 then b.Euro else null end MW2,
case b.MW_ID when 3 then b.Euro else null end MW3,
case b.MW_ID when 4 then b.Euro else null end MW4,
case b.MW_ID when 5 then b.Euro else null end MW5,
case b.MW_ID when 6 then b.Euro else null end MW6
 from table1 a join table2 b
on (a.ID=b.ID and a.Kost_ID=b.Kost_ID )

ID  Kost_ID Col1    MW1     MW2     MW3     MW4     MW5     MW6
2016    1   bla     10      NULL    NULL    NULL    NULL    NULL
2016    2   bla     NULL    20      NULL    NULL    NULL    NULL
2016    3   bla1    NULL    NULL    NULL    NULL    NULL    30
2016    3   bla1    NULL    NULL    NULL    NULL    40      NULL
2016    4   abl     NULL    NULL    NULL    80      NULL    NULL
2016    5   NULL    NULL    NULL    NULL    NULL    50      NULL
2016    6   NULL    NULL    NULL    NULL    NULL    NULL    60
2016    7   NULL    NULL    NULL    70      NULL    NULL    NULL

Upvotes: 1

Massimo Petrus
Massimo Petrus

Reputation: 1891

If you don't want to use Pivot Queries you can try with left joins, maybe a little boring, manual and old style

select t1.*,
t21.euro mw1,t22.euro mw2, t23.euro mw3,t24.euro mw4,t25.euro mw5,t26.euro mw6
from 
table1 t1 left join table2 t21
on t1.id=t21.id
and t1.kost_id=t21.kost_id
and t21.mw_id=1
left join table2 t22
on t1.id=t22.id
and t1.kost_id=t22.kost_id
and t22.mw_id=2
left join table2 t23
on t1.id=t23.id
and t1.kost_id=t23.kost_id
and t23.mw_id=3
left join table2 t24
on t1.id=t24.id
and t1.kost_id=t24.kost_id
and t24.mw_id=4
left join table2 t25
on t1.id=t25.id
and t1.kost_id=t25.kost_id
and t25.mw_id=5
left join table2 t26
on t1.id=t26.id
and t1.kost_id=t26.kost_id
and t26.mw_id=6


order by t1.id, t1.kost_id

Upvotes: 2

Related Questions