CODEWITHSUNDEEP
mysql
Agung Kusumo
Agung Kusumo

Reputation: 11

Pivot Table Goes Wrong (Select Statement)

I have a table structured like this:

ID   , AMOUNT
'001', 17333935.00
'005', 1883045.00
'011', 29992400.00
'015', 17547235.00

I want to create a list like this:

| '001'       | '005'       | '011'       | '015' |

| 17333935.00 | 1883045.00  | 29992400.00 | 17547235.00 |

I attempted to use this Select statement, but it doesn't work.

SELECT col1.ROW_RPT,col1.IDUSER,col1.SUBTOT,col2.SUBTOT,col3.SUBTOT
 FROM
(SELECT 'OMSET' ROW_RPT,IDUSER,Sum(ORG_QTY*ORG_PRC) SUBTOT FROM sales WHERE IDUSER='001') col1 
INNER JOIN
(SELECT 'OMSET' ROW_RPT,IDUSER,Sum(ORG_QTY*ORG_PRC) SUBTOT FROM sales WHERE IDUSER='005') col2 ON col2.ROW_RPT=col1.ROW_RPT INNER JOIN
(SELECT 'OMSET' ROW_RPT,IDUSER,Sum(ORG_QTY*ORG_PRC) SUBTOT FROM sales WHERE IDUSER='011') col3 ON col3.ROW_RPT=col1.ROW_RPT INNER JOIN
(SELECT 'OMSET' ROW_RPT,IDUSER,Sum(ORG_QTY*ORG_PRC) SUBTOT FROM sales WHERE IDUSER='015') col4 ON col3.ROW_RPT=col1.ROW_RPT

How can I extract the data like above?

Upvotes: 0

Views: 62

Answers (1)

Devart
Devart

Reputation: 122032

You may use something like this -

SELECT
  MAX(IF(id = '001', AMOUNT, NULL)) AS '001',
  MAX(IF(id = '005', AMOUNT, NULL)) AS '005',
  ..
FROM
  sales

But I think it is not good idea, because there can be a lot of id.

Upvotes: 1

Related Questions