1

I'm utilizing MySQL 5.7 and need to construct an SQL query to establish a view. The purpose of this view creation is to facilitate our clients who are using Metabase for querying our database and generating graphs. The task at hand involves creating a separate column for each existing record in table_a. To illustrate, assume we have the following rows in table_a: 'one', 'two', 'three', I would require columns to be created accordingly.

table_b.value, one_amount, one_date, two_amount, two_date, three_amount, three_date
    Value1        45      2023-05-01   null        null         50       2023-08-15
    Value2        null      null       null        null         80       2023-04-10

Here is my schema

CREATE TABLE `table_a` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `value` varchar(255)
);

CREATE TABLE `table_b` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `value` varchar(255) DEFAULT NULL
);

CREATE TABLE `table_c` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `table_a_id` bigint(20) UNSIGNED DEFAULT NULL,
  `table_b_id` bigint(20) UNSIGNED DEFAULT NULL,
  `amount` int DEFAULT 0,
  `date` date DEFAULT NULL
);

It is possible to do this with MySQL ? Or there is another way to do that on Metabase ?

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
John
  • 4,711
  • 9
  • 51
  • 101
  • You need to use CONCAT to use a result as a column name. Build a stored procedure so you can do all 3 creates in parallel and be sure to use Start Transaction (your code) Commit to make them all literally work at the same time to keep the data correct. This should point you in the right direction --> https://www.w3schools.com/Sql/func_mysql_concat.asp – easleyfixed Jul 17 '23 at 15:16
  • So, when the table contains one million rows, you want a query result of one million columns? Is that correct? – Thorsten Kettner Jul 17 '23 at 15:23
  • @ThorstenKettner yes, but this table will contains max 20 rows it's a business table – John Jul 17 '23 at 15:27
  • Okay. A query results in a table and a table has defined columns. This means it is not possible for a query to return a variable number of columns. Would it hence be okay, if the query returned columns for 20 rows, filling only as many columns with values as there are rows in the table, leaving the other columns null? And where do you get the names "one", "two", "three" from? Can we just name the data from the first row we find "one"? And do you want the columns in the order of the rows ordered by ID or date or something else or doesn't the order matter at all? – Thorsten Kettner Jul 17 '23 at 15:57
  • @ThorstenKettner There's no need for a specific order of columns, just ensure to have 'one_amount' and 'one_date' next to each other. The names "one", "two", "three" are variable values; users can input any data. In the actual case, it's simply a configuration table where users can enter their list of financiers. In my application, the list is variable but will always be short. – John Jul 17 '23 at 16:06
  • Ah, okay, the "one" is the value in table_a. I see. Sorry. To convert this data into column names, you need dynamic SQL. This means you could write and call a function for that, but then I doubt that MySQL allows functions returning rows from a dynamically built query. I don't use MySQL and don't know the answer. I'd say your chances are slim. – Thorsten Kettner Jul 17 '23 at 17:35
  • I've added appropriate tags to your request in hopes to attract someone more versed in this topic. – Thorsten Kettner Jul 17 '23 at 17:38
  • @ThorstenKettner Okay, thank you. At the moment, the only solution I've found is to handle it on the application side. For example, when I add, modify, or remove an element from table_a, I delete and recreate my SQL view with the new fields. – John Jul 17 '23 at 17:53
  • You cannot define a view that returns a variable number of columns, nor can a view handle dynamically named columns. My advice is to build an efficient view for the raw data (in traditional columns) and do the pivot in your front-end. – Paul Maxwell Jul 17 '23 at 23:46
  • Metabase does support pivot tables - I would definitely explore that option. – Paul Maxwell Jul 17 '23 at 23:54

0 Answers0