How to pivot a single table in MySQL script?

Question

I have SQL Table that looks like this :

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

And This :

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

I would like to create a pivot table so that PARAMVALUE became column name VALUE.

Like this :

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

Fyi, this is a single table. and many other question is asking for joining 2 different table. I don't think it's duplicated to any one else.

I don't think its a duplicated. since my question is about a single table. and that one you mention is a solution for pivoting 2 table ( a join table ). cmiiw — White Mask Guy, Feb 07 '19 at 09:24

score 1 · Accepted Answer · answered Feb 06 '19 at 08:12

Hi i think you can get excepted result using :

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;

But if you have other column dynamicaly you have to use Dynamic SQL query using EXECUTE example was find here : MySQL pivot table query with dynamic columns

Wow it still works like a charm! Thanks for your help ! You're the real life savior !! — White Mask Guy, Feb 06 '19 at 08:29

How to pivot a single table in MySQL script?

1 Answers1