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