CODEWITHSUNDEEP
mysqlsql
sumpreto
sumpreto

Reputation: 145

Insert..Select, same column different values

I've got this table with the following:

part_id  |  feature_name  | feature_value  |  feature_unit  |
_____________________________________________________________
   1     |     Weight     |      2         |       kg       |
   1     |     Color      |     Blue       |                |
   1     |   Description  |description here|                |

What i wanted to do was place these in another (new)table

part_id  |   description  |  color  | weight  |
_______________________________________________
   1     |description here|   Blue  |   2kg   |

I thought of using the Insert..Select depending on the value of the feature_value column but cant seem to build the right query for this.Any ideas?

Upvotes: 0

Views: 287

Answers (3)

radar
radar

Reputation: 13425

You are converting columns to rows, you can use case based aggregation for this.

INSERT into newTable(part_id, description, color, weight)
SELECT part_id
       max( case when feature_name ='Description' 
                 then feature_value end ) as description,
       max( case when feature_name ='Color' 
                 then feature_value end ) as Color,
       max( case when feature_name ='weight' 
                 then concat(feature_value,feature_unit) end ) as weight
FROM my_table
GROUP BY part_id

Upvotes: 1

Augusto
Augusto

Reputation: 1336

Use this select 

select t1.part_id, t1.feature_value, t2.feature_value, t3.feature_value from table t1 inner join table t2 on t1.part_id = t2.part_id and t2.feature_name = 'Color' inner join table t3 on t1.part_id =t3.part_id and t3.feature_name='Weight' where t1.feature_name = 'Description'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

One way to do this is with conditional aggregation:

select part_id,
       max(case when feature_name = 'description' then feature_value end) as description,
       max(case when feature_name = 'color' then feature_value end) as color,
       max(case when feature_name = 'weight' then concat(feature_value, feature_unit) end) as weight
from thistable
group by part_id;

Upvotes: 0

Related Questions