嗨,我想您可以使用以下方法获得例外结果:
create temporary table tempTable
(
PARAMNAME varchar(40) not null, PARAMVALUE varchar(40) not null, INTERFACEID varchar(40) not null
);
INSERT INTO TEMPTABLE ()
SELECT 'billingInfo1', '021076427070122', 'MPN.INQUIRY.DJA'
UNION
SELECT 'billingInfo2', '411122','MPN.INQUIRY.DJA'
UNION
SELECT 'billingInfo3','100','MPN.INQUIRY.DJA'
UNION
SELECT 'billingInfo4','03032014','MPN.INQUIRY.DJA'
UNION
SELECT 'billingInfo5','000000000000000','MPN.INQUIRY.DJA'
UNION
SELECT 'billingInfo6','JL.MESJID IV NO.19,JAKARTA UTARA','MPN.INQUIRY.DJA'
UNION
SELECT 'billingInfo7','900019191818181778','MPN.INQUIRY.DJA'
UNION
SELECT 'amount','89002','MPN.INQUIRY.DJA'
UNION
SELECT 'customerName','NPWP DUMMY DJA','MPN.INQUIRY.DJA'
UNION
SELECT 'responseCode','00','MPN.INQUIRY.DJA';
-- SELECT * FROM TEMPTABLE;
SELECT INTERFACEID,
MAX(CASE WHEN PARAMNAME = 'billingInfo1' THEN PARAMVALUE ELSE NULL END) AS 'billingInfo1',
MAX(CASE WHEN PARAMNAME = 'billingInfo2' THEN PARAMVALUE ELSE NULL END) AS 'billingInfo2',
MAX(CASE WHEN PARAMNAME = 'billingInfo3' THEN PARAMVALUE ELSE NULL END) AS 'billingInfo3',
MAX(CASE WHEN PARAMNAME = 'billingInfo4' THEN PARAMVALUE ELSE NULL END) AS 'billingInfo4',
MAX(CASE WHEN PARAMNAME = 'billingInfo5' THEN PARAMVALUE ELSE NULL END) AS 'billingInfo5',
MAX(CASE WHEN PARAMNAME = 'billingInfo6' THEN PARAMVALUE ELSE NULL END) AS 'billingInfo6',
MAX(CASE WHEN PARAMNAME = 'billingInfo7' THEN PARAMVALUE ELSE NULL END) AS 'billingInfo7',
MAX(CASE WHEN PARAMNAME = 'amount' THEN PARAMVALUE ELSE NULL END) AS 'amount',
MAX(CASE WHEN PARAMNAME = 'customerName' THEN PARAMVALUE ELSE NULL END) AS 'customerName',
MAX(CASE WHEN PARAMNAME = 'responseCode' THEN PARAMVALUE ELSE NULL END) AS 'responseCode'
FROM TEMPTABLE
GROUP BY INTERFACEID;
drop temporary table temptable;
但是,如果有其他列dynamicaly,则必须使用下面的EXECUTE example was来使用动态SQL查询:
MySQL pivot table query with dynamic columns
