MySQL query row as column

Question

I have a MySQL table like a table below.

Username | Region | Product
---------|--------|--------
Firdauss | CRO | DMC
Firdauss | ECO | DMC
Firdauss | EMO | DMC
Firdauss | NRO | DMC
Firdauss | SRO | DMC

Currently what I get with my query was like a table below

Product | CRO      | ECO       | EMO       | NRO       | SRO 
--------|-----     |-----      |-----      |-----      |----
DMC     | Firdauss | 0         | 0         | 0         | 0
DMC     | 0        | Firdauss  | 0         | 0         | 0
DMC     | 0        | 0         | Firdauss  | 0         | 0
DMC     | 0        | 0         | 0         | Firdauss  | 0
DMC     | 0        | 0         | 0         | 0         | Firdauss

And my query is

 select _PRODUCT,
 if(_REGION = 'CRO',concat(USR_FIRSTNAME," ",USR_LASTNAME," 
 (",DEP_TITLE,")"),0) as CRO
 ,if(_REGION = 'ECO',concat(USR_FIRSTNAME," ",USR_LASTNAME," 
 (",DEP_TITLE,")"), 0) as ECO
 ,if(_REGION = 'NRO',concat(USR_FIRSTNAME," ",USR_LASTNAME," 
 (",DEP_TITLE,")"), 0) as NRO
 ,if(_REGION = 'SRO',concat(USR_FIRSTNAME," ",USR_LASTNAME," 
 (",DEP_TITLE,")"), 0) as SRO
 ,if(_REGION = 'EMO',concat(USR_FIRSTNAME," ",USR_LASTNAME," 
 (",DEP_TITLE,")"), 0) as EMO
 from t1 order by _PRODUCT asc ;

What I'm trying to get is like a table below

Product | CRO      | ECO      | EMO      | NRO      | SRO 
--------|-----     |-----     |-----     |-----     |----
DMC     | Firdauss | Firdauss | Firdauss | Firdauss | Firdauss

May anyone helps me?

Thanks in advance.

score 0 · Answer 1 · answered Aug 23 '17 at 01:54

Aggregate over the product column:

SELECT
    _PRODUCT,
    MAX(CASE WHEN _REGION = 'CRO'
             THEN CONCAT(USR_FIRSTNAME, ' ', USR_LASTNAME, '(', DEP_TITLE, ')') END) CRO,
    MAX(CASE WHEN _REGION = 'ECO'
             THEN CONCAT(USR_FIRSTNAME, ' ', USR_LASTNAME, '(', DEP_TITLE, ')') END) ECO,
    MAX(CASE WHEN _REGION = 'NRO'
             THEN CONCAT(USR_FIRSTNAME, ' ', USR_LASTNAME, '(', DEP_TITLE, ')') END) NRO,
    MAX(CASE WHEN _REGION = 'SRO'
             THEN CONCAT(USR_FIRSTNAME, ' ', USR_LASTNAME, '(', DEP_TITLE, ')') END) SRO,
    MAX(CASE WHEN _REGION = 'EMO'
             THEN CONCAT(USR_FIRSTNAME, ' ', USR_LASTNAME, '(', DEP_TITLE, ')') END) EMO
FROM t1
GROUP BY _PRODUCT
ORDER BY _PRODUCT

Hi @tim-biegeleisen, this code working for me. But I facing other difficulties when having a multiple Username & Products. — Firdauss Noordin, Aug 23 '17 at 02:37

MySQL query row as column

1 Answers1