0

I have a table like status, size, datetime, total_day for the material stays in the place based on size.

I want to get a report like this

                        20          40

Year Category AF CM BF AF CM BF 2019 22 to Above 3 2 1 Days 1 to 5 1 1 2 1 1 Days 15 to 21
Days 6 to 7
Days 8 to 14

I have used the below script to extract data from my table

SELECT D.STATUS, D.SIZE, TO_CHAR(D.DATETIME, 'YYYY') AS YEAR,TOTAL_DAY,
CASE WHEN TOTAL_DAY BETWEEN 1 AND 5 THEN 'Days 1 to 5'
   ELSE '22 to Above'
END AS "dwCategory",
CASE 
WHEN D.SIZE BETWEEN '20' AND '25' THEN '20'
ELSE '40' 
END AS "SIZE_RANGE"
FROM  TOTAL_TIME D WHERE D.DATETIME BETWEEN '01-JUN-2019' AND '30-JUN-2019';

STATUS SIZE YEAR TOTAL DAY dwCategory SIZE_RANGE AF 22 2019 8 22 to Above 20 CM 22 2019 5 Days 1 to 5 20 BF 45 2019 6 22 to Above 40 AF 45 2019 6 22 to Above 40 AF 45 2019 5 Days 1 to 5 40 AF 45 2019 11 22 to Above 40 AF 22 2019 8 22 to Above 20 AF 22 2019 6 22 to Above 20 CM 45 2019 1 Days 1 to 5 40 AF 45 2019 2 Days 1 to 5 40 BF 22 2019 1 Days 1 to 5 20

Anyone can help me to tune the script to make the pivot table as I'm very new Oracle SQL

JSF Learner
  • 183
  • 2
  • 7
  • 15
  • 1
    please replace the pictures with text data. Somebody cannot see because of some filters, and using text data is easier within a fiddle. – Barbaros Özhan Jul 12 '19 at 06:52
  • I have tried but unable to achieve – JSF Learner Jul 12 '19 at 07:08
  • You want to pivot on two columns so categorize data as you did and use: `pivot (sum(total_day) for (size_range, status) in ((20, 'AF'), (40, 'AF'), (20, 'BF'), (40, 'BF'), (20, 'CM'), (40, 'CM')))`. Here is an [example](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=72d361c5a911872bb3cc724f410adc83). – Ponder Stibbons Jul 12 '19 at 11:29

0 Answers0