如何在MySQL脚本中旋转单个表？

Question

我有如下所示的SQL表：

select PARAMNAME, PARAMVALUE, INTERFACEID from RTVS_RESPONSE WHERE INTERFACEID IN ('MPN.INQUIRY.DJA')

billingInfo1 021076427070122 MPN.INQUIRY.DJA billingInfo2 411122 MPN.INQUIRY.DJA billingInfo3 100 MPN.INQUIRY.DJA billingInfo4 03032014 MPN.INQUIRY.DJA billingInfo5 0000000000000 MPN.INQUIRY.DJA billingInfo6 JL.MESJID IV IV.19，雅加达UTARA MPN.INQUIRY.DJA billingInfo7 900019191818181778 MPN.INQUIRY.DJA金额89002 MPN.INQUIRY.DJA customerName NPWP虚拟DJA MPN.INQUIRY.DJA responseCode 00 MPN.INQUIRY.DJA

这是：

select PARAMNAME, PARAMVALUE, INTERFACEID from RTVS_RESPONSE WHERE INTERFACEID IN ('MPN.INQUIRY.DJBC')

billingInfo1 021076427070122 MPN.INQUIRY.DJBC billingInfo2 411122 MPN.INQUIRY.DJBC billingInfo3 100 MPN.INQUIRY.DJBC billingInfo4 03032014 MPN.INQUIRY.DJBC billingInfo5 0000000000000 MPN.INQUIRY.DJBC billingInfo6 JL.MESJID IV IV.19，雅加达UTARA MPN.INQUIRY.DJBC billingInfo7 900019191818181778 MPN.INQUIRY.DJBC金额89001 MPN.INQUIRY.DJBC customerName NPWP虚拟DJBC MPN.INQUIRY.DJBC responseCode 00 MPN.INQUIRY.DJBC

我想创建一个枢轴表，这样PARAMVALUE就变成了column name VALUE。

就像这样：

INTERFACEID | billingInfo1 | billingInfo2 | billingInfo3 | billingInfo4 | billingInfo5 | billingInfo6 | billingInfo7 | amount | customerName | responseCode

这是一张单桌。还有很多其他的问题是要加入两张不同的桌子。我不认为它和其他人一样。

Answer 1

嗨，我想你可以通过以下方式得到例外的结果：

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;

但是，如果您有其他的动态列，则必须使用使用EXECUTE示例的动态SQL查询，在这里可以找到：MySQL pivot table query with dynamic columns