CODEWITHSUNDEEP
phpmysql
Dilip Tiwari
Dilip Tiwari

Reputation: 45

Want to create comparison matrix from MySQL table

Can anyone help me on creation of comparison matrix from mySQL table.
I have following table.

MRP | Brand | USAGE
10 | ABC | 200 MB
10 | XYZ | 300 MB
20 | ABC | 500 MB
30 | XYZ | 600 MB


I want a query which gives following result.

MRP | ABC | XYZ
10 | 200 MB | 300 MB
20 | 500 MB |-----------
30 | -----------| 600 MB

can I achieve this using PHP? thanks and regards.

Upvotes: 2

Views: 645

Answers (3)

Himanshu
Himanshu

Reputation: 32602

Try GROUP_CONCAT function to achieve this with CASE statement like this one:

SELECT MRP
     ,GROUP_CONCAT(CASE Brand WHEN 'ABC' THEN `USAGE` ELSE NULL END) AS ABC
     ,GROUP_CONCAT(CASE Brand WHEN 'XYZ' THEN `USAGE` ELSE NULL END) AS XYZ
FROM myTable GROUP BY MRP;

See this SQLFiddle

Upvotes: 1

John Woo
John Woo

Reputation: 263803

Try,

SELECT  MRP,
        MAX(CASE WHEN BRAND = 'ABC' THEN `USAGE` ELSE NULL END) 'ABC',
        MAX(CASE WHEN BRAND = 'XYZ' THEN `USAGE` ELSE NULL END) 'XYZ'
FROM tableName
GROUP BY MRP

SQLFiddle Demo

Upvotes: 1

Gavin Towey
Gavin Towey

Reputation: 3200

You want what's called a pivot table.

something like:

SELECT MRP,
    MAX(IF(Brand='ABC', USAGE,0)) as 'ABC',
    MAX(IF(Brand='XYZ', USAGE,0)) as 'XYZ'
FROM table
GROUP BY MRP;

See this article for more info: http://www.artfulsoftware.com/infotree/queries.php#78

Upvotes: 1

Related Questions