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