select row values to be column titles in mysql select [duplicate]

Question

I have two tables in MySql,

shareholders     

    id  name   value
     1  harry    20
     2  mark     60
     3  richard  20

the second table is

transactions    
     id   date          amount
      1   2013-11-01    2000
      2   2013-11-01    150
      3   2013-11-01    300
      4   2013-11-02    700
      5   2013-11-02    5400

the first table contains the percentage in which the amount in the transactions is divided. is it possible to have a select query in MySQL that will return the following output?

transid    amount    harry   mark   richard ,.....
1          2000      400     1200    400
2          150        30       90     30
3          300        60      180     60
.
.
.

the number of shareholders is not fixed

peterm · Accepted Answer · 2013-11-15 06:54:12Z

4

Use dynamic SQL for that

SET @sql = NULL;

SELECT GROUP_CONCAT(CONCAT(
          'MAX(CASE WHEN h.id = ', id, 
          ' THEN amount * ', value / 100, 
          ' END) `', name, '`'))
  INTO @sql
  FROM shareholders;

SET @sql = CONCAT(
              'SELECT t.id transaction_id, t.date, t.amount, ', @sql, 
              '  FROM transactions t CROSS JOIN shareholders h 
                GROUP BY t.id');

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

Output:

| TRANSACTION_ID |       DATE | AMOUNT | HARRY | MARK | RICHARD |
|----------------|------------|--------|-------|------|---------|
|              1 | 2013-11-01 |   2000 |   400 | 1200 |     400 |
|              2 | 2013-11-01 |    150 |    30 |   90 |      30 |
|              3 | 2013-11-01 |    300 |    60 |  180 |      60 |
|              4 | 2013-11-02 |    700 |   140 |  420 |     140 |
|              5 | 2013-11-02 |   5400 |  1080 | 3240 |    1080 |

Here is SQLFiddle demo

To simplify things on the calling end you can wrap it up in a stored procedure

DELIMITER $$
CREATE PROCEDURE shareholders_report()
BEGIN
  SET @sql = NULL;

  SELECT GROUP_CONCAT(CONCAT(
            'MAX(CASE WHEN h.id = ', id, 
            ' THEN amount * ', value / 100, 
            ' END) `', name, '`'))
    INTO @sql
    FROM shareholders;

  SET @sql = CONCAT(
                'SELECT t.id transaction_id, t.date, t.amount, ', @sql, 
                '  FROM transactions t CROSS JOIN shareholders h 
                  GROUP BY t.id');

  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

And then use it:

CALL shareholders_report();

Here is SQLFiddle demo

answered Nov 15, 2013 at 6:54

peterm

93k16 gold badges157 silver badges162 bronze badges

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

3 Comments

peterm Over a year ago

@indago You're quite welcome. Good luck :)

indago Over a year ago

i have bumped into a problem, can i create a view with the above code? Am getting an Error while creating a view.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET @sql = NULL' at line 2

peterm Over a year ago

@indago No you can't. MySQL doesn't allow neither subqueries in FROM nor dynamic SQL (PREPARE and all)

Collectives™ on Stack Overflow

select row values to be column titles in mysql select [duplicate]

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related