Combining Many Columns into Few

Get Help
1

Hello, first off let me say I've been using Metabase for the organization I work for, for over a year now and we're quite happy with it. I've been able to solve most issues on my own, because - let's face it, folks aren't typically creative in how they store their data; until now.

Working with an SQL database (MariaDB) and what I'm trying to do in Metabase, I just can't seem to figure it out or whether it's even possible:

Each row of data has the following columns...

  • Name
  • Date 1
  • Data Type 1 (1-6)
  • Quantity 1
  • Date 2
  • Data Type 2 (1-6)
  • Quantity 2
  • ...
  • Date 10
  • Data Type 10 (1-6)
  • Quantity 10

Yup, so each row has 10 repetitions of Date, Data Type, and Quantity. There are six data types, so the preference would be to show the quantity for the data type in its own column.

  • Name
  • Date
  • Type 1 Quantity
  • Type 2 Quantity
  • ...
  • Type 6 Quantity

I hope that makes sense - any thoughts?

2

Hi @jweese
I would recommend that you try asking in a forum dedicated to your database or stackoverflow.com, since this will require some custom SQL and the question is more ETL related than anything to do with Metabase.

1 Like
3 
Select Name, Date1, DataType1, Quantity1
UNION ALL
Select Name, Date2, DataType2, Quantity2
UNION ALL
etc, etc

Use UNION ALL rather than UNION for performance. Consider adding a column to the end to specify whether it's date1 or date 2 etc. just to help debugging.
From working with Metabase for the last few years, I'd say that you should never use the database's tables. Always create views of what you need. Saves a load of bother in the long run.