Horizontal -> Vertical, multiple values?

sql
lookml

(mike) #1

I have flipped a table from horizontal into vertical using the following SQL:

SELECT
Client,
max(case when role = ‘Masseuse’ then user else null end) as Masseuse_lead,
max(case when role = ‘Retail’ then user else null end) as Retail_lead
from […]
group by 1

It works, but where it falls apart is if one client has two masseuses assigned to them. In that scenario, it will only pick one of the masseuses (the max). How can I make it so that it concatenates all of that client’s masseuses and fills that field with it? I would do a group concat in sql, but not sure what to do here.

Before you ask, yes, I am new to Looker! Please advise!


(quinn.morrison) #2

Hi Michael,

There are a couple of ways to transpose a data table in Looker. The simplest method is the you have outlined here. However, instead of pulling the max() value here, can we try just implementing the case when logic? When there are multiple masseuses per client, there should be multiple rows per client id. This technique is outlined here.

If this does not get you the set up you are looking for, we may want to look into implementing a more advanced modeling transformation, such as that outlined here. This resource deals with an EAV model, but we can apply the same logic to force our row values into columns. If you would like to explore this set up further, please reach out to support@looker.com!

Thanks,

Quinn