Hi Geeks,
i want to extract the ranks 1,2 and 3 into columns.
Here is my sql so far. The line with filter m.MOV_MAD_MAT_NO will be removed when the test phase is done and the statement works.
Background are spare part movements in storage logistics.
SELECT qty.SNR AS MatNo, SUM(qty.Occurrence) AS Occurrence, qty.Ammount AS Ammount
FROM
(SELECT m.MOV_MAD_MAT_NO AS SNR, SUM(m.MOV_QTY) AS Ammount, 1 AS Occurrence
FROM MOVEMENT m WHERE m.MOV_CTIME>TO_DATE('30.09.2018','DD.MM.YYYY')
AND m.MOV_MAD_MAT_NO='81152100118'
AND m.MOV_TYPE='K'
AND m.MOV_ORDER_NO LIKE 'AU%'
AND m.MOV_SRC_LOC_CODE LIKE 'L%'
AND MOV_QTY>0
GROUP BY m.MOV_MAD_MAT_NO, m.MOV_MAN_ORDER) qty
GROUP BY qty.SNR, qty.Ammount
ORDER BY Occurrence DESC
The current result is
MATNO OCCURRENCE AMMOUNT
81152100118 54 <- 1 <--needing this
81152100118 9 <- 2 <--and this
81152100118 4 <- 60 <--and this
81152100118 3 5
81152100118 2 4
81152100118 2 3
81152100118 1 35
81152100118 1 50
81152100118 1 40
81152100118 1 6
Now i'd like to have MATNO, 1st rank(OCCURRANCE), 1st rank(AMMOUNT), 2nd rank(OCCURRANCE), 2nd rank(AMMOUNT), 3rd rank(OCCURRANCE), 3rd rank(AMMOUNT)
Thanx in advance
Frankie.