On 23-3-2013 13:11, Mark Rotteveel wrote:

> 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

Is just realised that pivot_step2 isn't even necessary because of the
requirement that the combination id of columnName is unique, so the
following will work as well:

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
)
SELECT id, col1, col2
FROM pivot_step1



--
Mark Rotteveel