CODEWITHSUNDEEP
mysqlselectrowstranspose
James Mwaiponya
James Mwaiponya

Reputation: 77

Turn mysql table rows into columns

I have the following two tables:

CREATE TABLE asset (
sid int(11) NOT NULL AUTO_INCREMENT,
asset_code varchar(32),
notes longtext,
PRIMARY KEY ('sid'),
kEY 'index_asset' ('asset_code'),
)

CREATE TABLE asset_data (
  asset_code varchar(32) NOT NULL ,
  sid int(10) unsigned NOT NULL,
  field_name varchar(128) NOT NULL,
  field_value mediumtext NOT NULL,
  PRIMARY KEY (sid,field_name),
  KEY asset_code (asset_code),
  KEY sid_asset_code (sid,asset_code)
)

The sample data in the tables is as follows:

INSERT INTO asset(asset_code,notes)
       values('asset001','aaaaaaaa'),
       ('asset002','bbbbbbbb');

INSERT INTO asset_data(asset_code,sid,field_name,field_value)
       values('asset001',1,'location','LL'),
       ('asset001',1,'mass','20kg'),
       ('asset001',1,'date_of_purchase','2005-04-17'),   
       ('asset002',2,'location','CK'),
       ('asset002',2,'mass','190kg'),
       ('asset002',2,'date_of_purchase','2010-04-03');

How can I display this data so that the data should look as shown below:

asset_code   sid     location      mass      date_of_purchase
 asset001      1         LL          20kg     2005-04-17
 asset002      2         CK         190kg     2010-04-03

Upvotes: 0

Views: 63

Answers (1)

Raymond Nijland
Raymond Nijland

Reputation: 11602

This process is called pivot. This is done with GROUP BY in combination with MAX and CASE

Query

SELECT
   asset_data.asset_code
 , asset_data.sid
 , MAX(
     CASE
       WHEN asset_data.field_name = 'location'
       THEN asset_data.field_value
      END
   ) AS location
 , MAX(
     CASE
       WHEN asset_data.field_name = 'mass'
       THEN asset_data.field_value
     END
   ) AS mass
 , MAX(
     CASE
       WHEN asset_data.field_name = 'date_of_purchase'
       THEN asset_data.field_value
      END
    ) AS date_of_purchase      
FROM 
 asset_data
GROUP BY
   asset_data.asset_code
 , asset_data.sid

Result

asset_code     sid  location  mass    date_of_purchase  
----------  ------  --------  ------  ------------------
asset001         1  LL        20kg    2005-04-17        
asset002         2  CK        190kg   2010-04-03

Upvotes: 2

Related Questions