mysql 动态查询_MySQL选择查询以生成动态列结果

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..

解决方案

You need to use Dynamic pivot

Create your pivot query Dynamically.

The main steps are as follows

Declare a variable @sql to carry your SUM function and CASW WHEN Expression

use CONCAT to combine your SUM function and CASW WHEN Expression string and main select string.

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