2

I have a Firebird sql query that returns 3 fields: Surname, Name and date as (YYYY-MM-DD HH:mm:ss)

select Surname, Name, "WHEN" from users
left join attendant on (users.id = attendant.userid);

This is the result:

Surname Name    2018-04-11 08:35:37
Surname Name    2018-04-11 12:52:30
Surname Name    2018-04-11 13:44:18
Surname Name    2018-04-11 17:08:05
Surname Name    2018-04-12 08:31:17
Surname Name    2018-04-12 12:36:07
Surname Name    2018-04-12 13:28:30
Surname Name    2018-04-12 17:44:55

What I want to achieve is to put the date field of the same day (YYYY-MM-DD) in the same row.

Surname Name    2018-04-11 08:35:37 2018-04-11 12:52:30 2018-04-11 13:44:18 2018-04-11 17:08:05
Surname Name    2018-04-12 08:31:17 2018-04-12 12:36:07 2018-04-12 13:28:30 2018-04-12 17:44:55

Is this possible with Firebird?

1

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 LIST combined by a group by on the date part of the timestamp, for example:

select surname, name, list("WHEN", ' ')
from users
left join attendant on users.id = attendant.userid
group by surname, name, cast("WHEN" as date)

Be aware: LIST does not guarantee an order. You may need to use an intermediate order by on "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").

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.