JPQL cross tab query
- by Phil
Hi can anyone tell me if its possible to write a cross tab query in JPQL?
(I'm using eclipse link JPA2)
An example of a cross tab query in SQL can found here http://onlamp.com/pub/a/onlamp/2003/12/04/crosstabs.html
SELECT
dept,
COUNT(CASE WHEN gender = 'm' THEN id ELSE NULL END) AS m,
COUNT(CASE WHEN gender = 'f' THEN id ELSE NULL END) AS f,
COUNT(*) AS total
FROM
person
GROUP BY
dept
How can I do the same thing as a single query in JPQL?
Looking at the spec it doesn't seem to look like CASE is valid in COUNT
Is there any other way?