0

I have a view in Oracle that uses PIVOT and I would like to apply it to an h2 database. However, Pivot is not yet supported in h2.

I am currently using CASE statements to do the pivot which is fine for now.

However, I am intrigued by user defined functions in h2.

Is it possible to create a generic PIVOT function in h2 by using user defined functions? Has anyone already done it?

opticyclic
  • 7,412
  • 12
  • 81
  • 155

1 Answers1

0

Oracle and SQL Server are the only popular databases to have a PIVOT (or UNPIVOT) clause, which generates several aggregate columns for input rows.

In all other databases, you'll have to resort to do manual pivoting, the way you did.

Stack Overflow is full of such questions for MySQL, for instance:

Community
  • 1
  • 1
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • h2 has user defined functions though. e.g. I have made Oracles TO_DATE function available in h2 this way. I'm wondering how far the functions can go. – opticyclic Nov 12 '15 at 14:52
  • @opticyclic: Yes, but `PIVOT` is not a scalar function. It transforms tables just like `GROUP BY` does. I doubt that this is possible in H2. If you want to be sure, I suggest asking this question on the [h2 user group](https://groups.google.com/forum/#!forum/h2-database) – Lukas Eder Nov 12 '15 at 15:29