Forum Stats

  • 3,874,720 Users
  • 2,266,768 Discussions


Optional FOR clause in PIVOT Statement

nemecj Member Posts: 41 Bronze Badge
edited Nov 18, 2018 8:25AM in Database Ideas - Ideas

I know that PIVOT and GROUP BY are completly different functionalities.

But in an edge case where ony one key value is pivoted, the result is identical.

If the FOR clause in PIVOT would be optional, I could write

select C1, C2, MAX_C3, CNT from  tab

pivot (max(c3) as max_c3, count(*) as cnt)

which would  produce identical result as following GROUP BY query

select c1, c2, max(c3) max_c3, count(*) cnt

from tab

group by c1, c2

  I'd for sure not want to push such construct in my production code, but for ad Hoc queries this could be a lightweight coverage of the GROUP BY 1,2 idea 

3 votes

Active · Last Updated