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?