2

Right now I'm having query like this - 

SELECT a, b, 
       SUM (CASE WHEN measure_name = 'ABC' THEN measure_qty END) OVER (PARTITION BY a, b )  AS   ABCPIVOT          
FROM data_app.work_test

Now as TD15 is supporting direct PIVOTING. How do I include this partition by in PIVOT function?

XYZ123
  • 21
  • 3
  • 2
    PIVOT is aggregating, but you're doing *Windowed Aggregates*. And even if there was a way to PIVOT it will not improve performance (PIVOT is rewritten to SUM/MAX/Etc. over CASE internally). – dnoeth Mar 27 '19 at 15:02
  • Over a huge dataset it should improve the performance I think. As TD15 is supporting PIVOT , I was curious if there's any way of replacing the case statement with PIVOT. I couldn't find any docs related to including the partition by option . – XYZ123 Mar 27 '19 at 15:19
  • 2
    As I wrote, PIVOT is internally rewritten using MAX over CASE, thus performance will be exactly the same. And it can't be used with OVER. – dnoeth Mar 27 '19 at 18:07

0 Answers0