Hello everyone,
I’m facing a challenge with dynamically generating columns in a SQL query based on field labels, and I’m wondering if it’s possible to achieve this using Metabase.
Context:
I have a database where one of the tables (foo) stores leads information, and another table (bar) stores additional information as key-value pairs (field labels and corresponding values). I would like to retrieve the foo data and dynamically create separate columns for each unique field_label found in the bar table. The problem is that I don’t know the field labels in advance.
Current Query Example:
Here’s a simplified version of the SQL query I’m working with:
SELECT
foo.id AS foo_id,
CONCAT(users.first_name, ' ', users.last_name) AS user_name,
ARRAY_AGG(CONCAT(f.label, ': ', bar.field_value)) AS lead_fields -- Here, I aggregate the field labels and values into a single array
FROM foo
LEFT JOIN users ON foo.user_id = users.id
LEFT JOIN bar ON bar.foo_id = foo.id
LEFT JOIN fields f ON bar.field_id = f.id
GROUP BY foo.id, users.first_name, users.last_name;
Expected Output:
Currently, all field labels and values are grouped together in a single column. However, what I want is to have separate columns for each field_label. For example:
foo_id |
user_name |
field_label |
field_value |
|---|---|---|---|
| 1 | Alice | Name | Alice Doe |
| 1 | Alice | Code | ABC123 |
| 2 | Bob | Name | Bob Smith |
| 2 | Bob | Code | XYZ789 |
What I’d like to achieve is this format:
foo_id |
user_name |
Name |
Code |
|---|---|---|---|
| 1 | Alice | Alice Doe | ABC123 |
| 2 | Bob | Bob Smith | XYZ789 |
Question:
Is it possible to dynamically create separate columns for each field_label in SQL (preferably using Metabase)? If so, could you provide some guidance on how to achieve this?
Thanks in advance for your help!
Let me know if you need any adjustments!