On 22-3-2013 22:16, Ronan van Riet wrote:

> Hello.
>
> I was wondering whether FB 2.5 supports pivot/unpivot operators, like supported in Oracle?
> If not is there perhaps a workaround?

No, Firebird does not have pivot/unpivot, see
http://tracker.firebirdsql.org/browse/CORE-1738

A workaround for PIVOT would involve something like:

WITH pivot_step1 AS (
SELECT id,
(SELECT columnValue FROM keyvalue WHERE id = a.id AND columnName
= 'col1') AS col1,
(SELECT columnValue FROM keyvalue WHERE id = a.id AND columnName
= 'col2') AS col2
FROM (SELECT DISTINCT id FROM keyvalue) a
),
pivot_step2 AS (
SELECT id, MAX(col1) AS col1, MAX(col2) AS col2
FROM pivot_step1
GROUP BY id
)
SELECT id, col1, col2
FROM pivot_step2

This does assume that the combination of id and columnName is unique!

The unpivot for this would be:

SELECT id, 'col1' AS columnName, col1 AS columnValue
FROM PIVOTEXAMPLE
UNION
SELECT id, 'col2' AS columnName, col2 AS columnValue
FROM PIVOTEXAMPLE

Mark
--
Mark Rotteveel