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 Answer 1

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").

This site is temporarily in read-only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .