Pivot data

[Jacco_De_Zwart]

Hi there data-friends,

I have the following (part of) data from a json file :

The first column is the ID, the last is a timestamp for that ID.

1514642225|dispatch.status.arrival|2022-09-21T08:28:00+02:00|2022-09-21T10:44:23.000+02:00

1514642225|dispatch.status.loading.begin|2022-09-21T09:44:00+02:00|2022-09-21T10:44:23.000+02:00

1514642225|dispatch.status.loading.end|2022-09-21T09:44:00+02:00|2022-09-21T10:44:23.000+02:00

1514642225|dispatch.status.departure|2022-09-21T09:44:00+02:00|2022-09-21T10:44:23.000+02:00

Now I want the following result

ID timestamp dispatch.status.arrival dispatch.status.loading.begin dispatch.status.loading.end dispatch.status.departure

1514642225 2022-09-21T10:44:23.000+02:00 2022-09-21T08:28:00+02:00 2022-09-21T09:44:00+02:00 2022-09-21T09:44:00+02:00 2022-09-21T09:44:00+02:00

How do I do that ???

Thanks in advance.

Jacco

[Anonymous]

Hi

Take a look at tDenormalize component, here is a similar post.

Regards

Shong

[Jacco_De_Zwart]

Thanks for the advise. But that is not really that I want.

I want the values for dispatch.status.arrival, dispatch.status.loading.begin, dispatch.status.loading.end and dispatch.status.departure in an separte column. Not all the values in one column separated by a ; or |. Allso, I don't need any grouping function like, count() or sum().

I have written an query in SQL that does the trick and is useful for now.

Jacco