CODEWITHSUNDEEP
sqlsql-servert-sqlpivotcase
Attaroqqi
Attaroqqi

Reputation: 77

How to join select two tables with condition on column sql

I have a table in sql server structured like this table1

----------------------------
| ID | year | code | value |
----------------------------
|  1 | 2019 |  41  |  33   |
|  1 | 2019 |  42  |  34   |
|  2 | 2019 |  42  |  35   |
|  2 | 2019 |  43  |  36   |
----------------------------

and this table2

---------------------
| ID | year | index |
---------------------
|  1 | 2019 |  15   |
|  2 | 2019 |  16   |
---------------------

and somehow I want to select join them and become like this 

---------------------------------------------------------------------
| ID | year | index | value_code_41 | value_code_42 | value_code_43 |
---------------------------------------------------------------------
|  1 | 2019 |  15   |      33       |      34       |       0       |
|  2 | 2019 |  16   |       0       |      35       |      36       |
---------------------------------------------------------------------

I already try case like this but it return 0 in value_code_42 and value_code_43 

select  b.*,
        case when a.value !=0 and kode=41 then a.value else 0 end as value_code_41,
        case when a.value !=0 and kode=42 then a.value else 0 end as value_code_42,
        case when a.value !=0 and kode=43 then a.value else 0 end as value_code_43
from table1 a, table2 b
where a.year=b.year and a.id=b.id

what is wrong with my select case?

Upvotes: 0

Views: 37

Answers (1)

GMB
GMB

Reputation: 222582

You need to aggregate to pivot the dataset:

select  
    b.id,
    b.year, 
    b.index,
    max(case when a.kode = 41 then a.value else 0 end) as value_code_41,
    max(case when a.kode = 42 then a.value else 0 end) as value_code_42,
    max(case when a.kode = 43 then a.value else 0 end) as value_code_43
from table1 a
inner join table2 b on a.year = b.year and a.id = b.id
group by b.id, b.year, b.index

Upvotes: 1

Related Questions