Den 05.01.2017 17:49, skrev 'Stef' [email protected] [firebird-support]:
> Hi Karol
>
> Thank you for the feedback, I understand what you mean, but how would I go
> about adding or changing to get the “group by”?
Hi Stef!
I'm not Karol, but being that you want them on one line, I think you
need to include them in an aggregate function, e.g. like this (using a
CTE just to avoid repeating the extract):
WITH TMP(DRIVERNR, MYWEEKDAY, PLANSTATUS) AS
(SELECT DRIVERNR, EXTRACT(WEEKDAY FROM PLANDATE), PLANSTATUS
FROM DRIVERPLAN
WHERE PLANDATE BETWEEN '2017/01/01' and '2017/01/07')
SELECT D.NAME,
LIST(DISTINCT IIF(P.MYWEEKDAY = 1, P.PLANSTATUS, null)) Monday,
LIST(DISTINCT IIF(P.MYWEEKDAY = 2, P.PLANSTATUS, null)) Tuesday,
LIST(DISTINCT IIF(P.MYWEEKDAY = 3, P.PLANSTATUS, null)) Wednesday,
LIST(DISTINCT IIF(P.MYWEEKDAY = 4, P.PLANSTATUS, null)) Thursday,
LIST(DISTINCT IIF(P.MYWEEKDAY = 5, P.PLANSTATUS, null)) Friday,
LIST(DISTINCT IIF(P.MYWEEKDAY = 6, P.PLANSTATUS, null)) Saturday,
LIST(DISTINCT IIF(P.MYWEEKDAY = 0, P.PLANSTATUS, null)) Sunday
FROM TMP P
JOIN DRIVERS D ON P.DRIVERNR = D.DRIVERNR
GROUP BY 1
Of course, if your select includes more than one week, then Monday may
either end up with one value or several values separated by comma. If
you want to select two weeks and have one row for each week returned,
then you would have to modify the query a bit (I'm thinking either using
EXECUTE BLOCK or several CTEs).
Generally speaking, I'd say what you want is a simplified version of
pivot tables in Excel, and turning rows into columns is something
Firebird hasn't been very good at (cases like yours where you know the
names of the columns are rather simple, but suppose you wanted each
column to contain the name of the drivers, then this would mean to
return a dynamic number of fields with dynamic field names, and this can
be quite hard to do with Firebird).
HTH,
Set
