If it is faster you have to measure for yourself. However, doing it on the db side sends less data across, so I would assume it to be faster.
The pivot itself is fairly simple. I put your query's result in a table to make the example simpler.
SQL Fiddle
PostgreSQL 9.1.9 Schema Setup:
CREATE TABLE your_query
("rel_id" int, "timestamp" timestamp, "y" varchar(1))
;
INSERT INTO your_query
("rel_id", "timestamp", "y")
VALUES
(1, '2013-01-01 00:00:00', 'a'),
(1, '2013-01-02 00:00:00', 'b'),
(1, '2013-01-03 00:00:00', 'c'),
(1, '2013-01-04 00:00:00', 'd'),
(2, '2013-01-01 00:00:00', 'e'),
(2, '2013-01-04 00:00:00', 'f'),
(2, '2013-01-06 00:00:00', 'g')
;
First step is to only return one row per date. That is simply done with a group by:
Query 1:
SELECT timestamp
FROM your_query
GROUP BY timestamp
ORDER BY timestamp
Results:
| TIMESTAMP |
|--------------------------------|
| January, 01 2013 00:00:00+0000 |
| January, 02 2013 00:00:00+0000 |
| January, 03 2013 00:00:00+0000 |
| January, 04 2013 00:00:00+0000 |
| January, 06 2013 00:00:00+0000 |
Now wee need to pull the "correct" value into each column. For that we combine an aggregate with a case. The case returns null for all rows for which the condition is not met. the aggregate ignores nulls. That leaves the one value we are looking for:
Query 2:
SELECT timestamp,
MAX(CASE WHEN rel_id = 1 THEN y END ) AS "1",
MAX(CASE WHEN rel_id = 2 THEN y END ) AS "2"
FROM your_query
GROUP BY timestamp
ORDER BY timestamp
Results:
| TIMESTAMP | 1 | 2 |
|--------------------------------|--------|--------|
| January, 01 2013 00:00:00+0000 | a | e |
| January, 02 2013 00:00:00+0000 | b | (null) |
| January, 03 2013 00:00:00+0000 | c | (null) |
| January, 04 2013 00:00:00+0000 | d | f |
| January, 06 2013 00:00:00+0000 | (null) | g |
To make this work with your original query just replace your_query in my example with
(
(SELECT rel_id, timestmap, y FROM table_1 AS full_
WHERE full_.timestamp BETWEEN %s AND %s
ORDER BY full_.timestamp)
UNION ALL
(SELECT rel_id, timestamp, y FROM table_2 AS full_
WHERE full_.timestamp BETWEEN %s AND %s
ORDER BY full_.timestamp)
UNION ALL
...
) AS your_query
I'm really not sure if this is going to work in this Database of yours, but I made tests with much simpler values and tables in my Oracle XE database and it worked just fine, I would give it a go.
SELECT * FROM ENTR2012
INNER JOIN ITST2012
ON ENTR2012.ABREV=ITST2012.ABREV AND ENTR2012.NOTA=ITST2012.NOTA AND ENTR2012.VALTOTAL=ITST2012.VALTOTAL
INNER JOIN PRODUTOS
ON ITST2012.ABREV=PRODUTOS.ABREV AND ITST2012.CODPRODUTO=PRODUTOS.CODIGO
INNER JOIN FORNEC
ON ITST2012.NUMEROEMIT=FORNEC.CNPJCPF;
Please, review values, columns and such because maybe I got lost during the writing.
Best Answer
A pivot transposes rows to columns based on a discriminator column. Your example data doesn't show a discriminator column, and the data also seems to indicate that this could lead to a variable number of columns (depending on the attendance per date), which is not possible in Firebird. Instead it looks like you want to aggregate those dates in a single column.
Assuming that is what you want, you can use
LISTcombined by a group by on the date part of the timestamp, for example:Be aware:
LISTdoes not guarantee an order. You may need to use an intermediateorder byon"WHEN"in a sub-query as a work around, but that trick does not always work (especially as in this case we're grouping on a derivative of"WHEN").