PIVOTing Underlying Data

[PaulM1]

I have a DB table similar to the following:

ParentRecordId  FieldName       DataType    Value
1               Date Started    DateTime    2020-07-30T12:30:00Z
1               Date Ended      DateTime    2020-07-31T10:18:00Z
1               Code Number     String      D44-9287F
2               Date Started    DateTime    2020-07-30T15:40:00Z
2               Code Number     String      L51-1121X
2               Amount Billing  Currency    194.25
2               Completed       Boolean     FALSE

I need to flatten this data out so that it can be accessed via a Looker View, and then able to be joined to an Explore. For example:

ParentRecordId  DateStarted             DateEnded               CodeNumber  AmountBilled    Completed
1               2020-07-30T12:30:00Z    2020-07-31T10:18:00Z    D44-9287F   <NULL>          <NULL>
2               2020-07-30T15:40:00Z    <NULL>                  L51-1121X   194.25          FALSE

One of the problems here, is the lack of a field to aggregate on. So I don’t think that a simple SQL PIVOT will work. I’m using Snowflake on the backend, and I’m still researching options there. I’m just wondering if anyone here has been able to solve a problem like this with Looker (or otherwise).

[Cyril_MTL_Analy]

Hello @PaulM1!

Looks like a typical EAV model.

Looker has an article about it here.

But if you’re familiar with dbt, I think that would be easier to manage. Here is a great blog post explaining how.