mardi 4 août 2015

How to PIVOT and filter with two parameter?

I have such SQ

  SELECT *   FROM (
               SELECT  dt.route_id,dc.card_type_id,dt.amount, dt.currency_id 
               FROM   ddrc_trans dt,  ddrc_card dc, ddrc_card_type drc 
               WHERE  ( dt.card_id = dc.id AND dt.route_id IN ( 1,2)  ) 
    )

Which returns such result

1   1   2   50  3
2   1   2   50  3
3   1   2   50  3
4   2   1   50  1
5   2   1   50  1

OK that looks nice!

I write pivot, in order to convert rows to columns.

SELECT *   FROM (
           SELECT  dt.route_id,dc.card_type_id,dt.amount, dt.currency_id 
           FROM   ddrc_trans dt,  ddrc_card dc, ddrc_card_type drc 
           WHERE  ( dt.card_id = dc.id AND dt.route_id IN ( 1,2)  ) 
) 
pivot
(
    SUM(amount) as AMOUNT_DATA
    FOR card_type_id IN (1,2) 
) 

It looks perfect!

1   2   1   900  NULL
2   2   3   NULL 1050
3   1   2   1050 NULL   
4   1   3   NULL 900
5   1   1   1050 NULL   
6   2   2   1050 NULL   

But 1. what should I do in order to filter with curency id? for instance I need to show only currency id. 2. How can I group with currency id? I do not want to be duplicated CURRENTY_ID.

I have do my solution like this, is there another way without concat?

SELECT *
FROM (
  SELECT dt.route_id, dt.amount, concat(concat( dt.currency_id , ' '), dc.card_type_id) as conval
         FROM   ddrc_trans dt, 
                ddrc_card dc, 
                ddrc_card_type drc 
         WHERE  ( dt.card_id = dc.id 
                  AND dt.route_id IN ( 1,2) 
                  AND drc.id IN ( 1,2,3 ) 
                  ) 
)  s
PIVOT
(
    SUM(amount) AM
    FOR conval IN ('5 1', '5 2', '5 3')
) pivot1

Aucun commentaire:

Enregistrer un commentaire