Making Multiple Rows into Multiple columns in mysql

Question

consider the following table

Name                                      Null?    Type
----------------------------------------- -------- --------------
PRODID                                             NUMBER
PRODNAME                                           VARCHAR2(50)

 Name                                      Null?    Type
 ----------------------------------------- -------- --------------
 PRODID                                             NUMBER
 PROPID                                             NUMBER
 PRONAME                                            VARCHAR2(100)
 PROVALUE                                           VARCHAR2(100)

The Sample data are as follows

 select * from prodmas;
    PRODID PRODNAME
---------- --------------
       101 Surf
       102 Ariel
 select * from prodprop
 /

PRODID     PROPID PRONAME   PROVALUE                     
------ ---------- -----------------------------------------------
   101       1001 Price     100
   101       1002 color     Blue
   101       1003 Agent     AV
   102       1001 Price     95
   102       1002 Color     Orange
.....

A Normal join query like this

 select prodname,provalue
 from prodmas pm
 inner join prodprop pp
 on pm.prodid = pp.prodid
 /
PRODNAME                                           PROVALUE
-------------------------------------------------- -----------
Surf                                               100
Surf                                               Blue
Surf                                               AV
Ariel                                              95
Ariel                                              Orange

But the expected output should be like this

Surf    100   Blue   AV 
Ariel    95   Orange AV
.......

Hope iam clearer

John Woo · Accepted Answer · 2013-03-25 08:33:06Z

3

SELECT  a.ProdID,
        a.ProdName,
        MAX(CASE WHEN b.ProName = 'Price' THEN b.ProValue ELSE NULL END) Price,
        MAX(CASE WHEN b.ProName = 'Color' THEN b.ProValue ELSE NULL END) Color,
        MAX(CASE WHEN b.ProName = 'Agent' THEN b.ProValue ELSE NULL END) Agent
FROM    prodmas a
        LEFT JOIN prodprop b
            ON .prodID = b.prodID
GROUP   BY a.ProdID, a.ProdName

SQLFiddle Demo

If you have unknown number of value for PRONAME, a Dynamic SQL is much preferred so there will be no other alterations required on the query.

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(case when PRONAME = ''',
      PRONAME,
      ''' then PROVALUE end) AS ',
      PRONAME
    )
  ) INTO @sql
FROM prodprop;

SET @sql = CONCAT('SELECT   a.ProdID,
                            a.ProdName, ', @sql, ' 
                  FROM    prodmas a
                          LEFT JOIN prodprop b
                              ON a.prodID = b.prodID
                  GROUP   BY a.ProdID, a.ProdName');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SQLFiddle Demo

THE OUTPUT from the queries above are the same:

╔════════╦══════════╦═══════╦════════╦════════╗
║ PRODID ║ PRODNAME ║ PRICE ║ COLOR  ║ AGENT  ║
╠════════╬══════════╬═══════╬════════╬════════╣
║    101 ║ Surf     ║   100 ║ Blue   ║ AV     ║
║    102 ║ Ariel    ║    95 ║ Orange ║ (null) ║
╚════════╩══════════╩═══════╩════════╩════════╝

edited Mar 25, 2013 at 8:33

answered Mar 25, 2013 at 8:27

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2202231 Over a year ago

let me test this in my original scenario

Ryan Griggs Over a year ago

Wow, you really deserve more than 3 upvotes - this is a great solution! Thanks!

lc. · Accepted Answer · 2013-03-25 08:27:21Z

-2

If you are ok specifying everything statically, you can do this with a join on prodprop for each property you want to retrieve:

SELECT p.prodname, 
    prop1001.provalue AS Price, 
    prop1002.provalue AS Color, 
    prop1003.provalue AS Agent
FROM prodmas p
LEFT OUTER JOIN prodprop prop1001 ON p.prodid = prop1001.prodid 
    AND prop1001.propid=1001
LEFT OUTER JOIN prodprop prop1002 ON p.prodid = prop1002.prodid
    AND prop1002.propid=1002
LEFT OUTER JOIN prodprop prop1003 ON p.prodid = prop1003.prodid
    AND prop1003.propid=1003

Otherwise, you are going to have to resort to dynamic SQL to create a custom query, or return the properties table as is, and do the formatting in the front end.

answered Mar 25, 2013 at 8:27

lc.

117k21 gold badges163 silver badges190 bronze badges

1 Comment

user2202231 Over a year ago

There are many such properties in the original scenario, will it not hamper the performances....

Collectives™ on Stack Overflow

Making Multiple Rows into Multiple columns in mysql

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related