mysql - 如何在 MySQL 脚本中透视单个表？

Question

这个问题已经有答案了:

MySQL pivot row into dynamic number of columns (1 个回答)

已关闭 4 年前。

我的 SQL 表如下所示:

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

PARAMNAME     PARAMVALUE                          INTERFACEID     
billingInfo1  021076427070122                     MPN.INQUIRY.DJA
billingInfo2  411122                              MPN.INQUIRY.DJA
billingInfo3  100                                 MPN.INQUIRY.DJA
billingInfo4  03032014                            MPN.INQUIRY.DJA
billingInfo5  000000000000000                     MPN.INQUIRY.DJA
billingInfo6  JL.MESJID IV NO.19,JAKARTA UTARA    MPN.INQUIRY.DJA
billingInfo7  900019191818181778                  MPN.INQUIRY.DJA
amount            89002                           MPN.INQUIRY.DJA
customerName  NPWP DUMMY DJA                      MPN.INQUIRY.DJA
responseCode  00                                  MPN.INQUIRY.DJA

还有这个:

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

PARAMNAME     PARAMVALUE                          INTERFACEID     
billingInfo1  021076427070122                     MPN.INQUIRY.DJBC
billingInfo2  411122                              MPN.INQUIRY.DJBC
billingInfo3  100                                 MPN.INQUIRY.DJBC
billingInfo4  03032014                            MPN.INQUIRY.DJBC
billingInfo5  000000000000000                     MPN.INQUIRY.DJBC
billingInfo6  JL.MESJID IV NO.19,JAKARTA UTARA    MPN.INQUIRY.DJBC
billingInfo7  900019191818181778                  MPN.INQUIRY.DJBC
amount            89001                           MPN.INQUIRY.DJBC
customerName  NPWP DUMMY 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

嗨，我认为您可以使用以下方法获得异常(exception)结果:

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