MySQL Select Query to generate dynamic column Resu

问题:

I need to write a query that returns a Column Dynamically. For example I have a table tblTest with columns:

Id, Name,   Type,  Amount
1, Receipt, Cash   100
2, Receipt, Card   200
3, Receipt, Cheque 250
4, Receipt, Card   150
5, Receipt, Cash   100
6, Payment, Cash   300
7, Payment, Cheque 400

SQL Query :

SELECT
    Name,
    SUM(CASE WHEN Type = 'Cash'   THEN Amount ELSE 0 END) Cash,
    SUM(CASE WHEN Type = 'Card'   THEN Amount ELSE 0 END) Card,
    SUM(CASE WHEN Type = 'Cheque' THEN Amount ELSE 0 END) Cheque
FROM tblTest
GROUP BY
    Name;

it returns me,

above result is as per my requirement but in my case Type Cash,Card,Cheque,etc in above data are not predefined, that may come dynamically then how could i manage it,Please help me to build dynamic columns SQL for that.

Thanks in advance..

回答1:

You need to use Dynamic pivot

Create your pivot query Dynamically.

The main steps are as follows

1. Declare a variable @sql to carry your SUM function and CASW WHEN Expression
2. use CONCAT to combine your SUM function and CASW WHEN Expression string and main select string.
3. use EXECUTE function execute SQL Dynamically.

look like this.

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN Type =''',
      Type,
      ''' THEN Amount END) AS ',
      Type
    )
  ) INTO @sql
FROM tblTest;

SET @sql = CONCAT('SELECT
                    Name,', @sql, ' 
                   FROM tblTest
                   GROUP BY
                    Name;');

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

Result

    Name    Cash    Card    Cheque
1   Payment 300     NULL    400
2   Receipt 200      350    250

SQLfiddle

回答2:

Hej,

Hope i got you right.

select name, type, sum(CASE WHEN Type in (select DISTINCT sub.type from 
tblTest sub) THEN Amount ELSE 0 END) as "sum" from tblTest
group by name, type;