CODEWITHSUNDEEP
mysqlsql
pansh
pansh

Reputation: 13

How to select different columns based on the value of specific column？

There are two tables with similar contents.I need to join them on the key 'id' to compare the value.They look like below:

table_A

id   item_name   item_value
1    cost         1.00
1    price        3.15
1    tax          0.15
2    cost         9.00
2    price        17.66
2    tax          1.00

table_B

id   cost   price   tax
1    1.20   3.80    0.20
2    8.95   15.65   0.98

what I want :

id   item_name   item_value_a   item_value_b
1    cost         1.00          1.20
1    price        3.15          3.80
1    tax          0.15          0.20
2    cost         9.00          8.95
2    price        17.66         15.65
2    tax          1.00          0.98

How to achieve this result by sql syntax?

I have tried using union all clause to convert table_B like below:

select id,'cost' as item_name,cost as item_value
from table_B 
union all
select id,'price' as item_name,price as item_value
from table_B
union all
select id,'tax' as item_name,tax as item_value
from table_B

I can get a table which is the same as table_A on table structure.Then I join them on id and item_name and select both item_value. I'm confused that if this method is the most efficient way to solve the issue.When there are plenty of columns,should I use so many union all clauses?

I use mysql version 8.0

thx for advice anyway.

Upvotes: 1

Views: 45

Answers (1)

Sergii Kudriavtsev
Sergii Kudriavtsev

Reputation: 10532

This is a nice use case for CASE operator:

SELECT a.id, a.item_name, a.price,
CASE a.item_name 
WHEN 'cost' THEN b.cost 
WHEN 'price' THEN b.price 
WHEN 'tax' THEN b.tax
END
FROM table_A a LEFT JOIN table_B b ON a.id=b.id;

Upvotes: 1

Related Questions