Mysql Question posted in
The official MySQL documentation can be found here.

MYSQL join tables with dynamic names

I have the following table:

id    |    name
1     |    Group1
2     |    Group2
..    |    ...

I want to join it with another table this way:

id    |    group_1    |    group_2    |    ..
1     |    10         |    8          |    .. 
2     |    12         |    6          |    ..

Hence Group1 with id 1 will be joined with the 2nd table group_1.

To group the columns and avoid:

id    |    group_id    |    value
1     |    1           |    10
2     |    1           |    12
3     |    2           |    8
4     |    2           |    6
..    |    ..          |    ..

Is it possible or is there an alternative way to achive the same result?

Tags:

2

Answers


  1. 0

    What you’re doing is often called an unpivot.

    SELECT
  ROW_NUMBER() OVER (ORDER BY g.id, v.id) AS id,
  g.id AS group_id,
  CASE g.id
  WHEN 1 THEN v.group_1
  WHEN 2 THEN v.group_2
  ...
  END AS value
FROM `the following table` AS g
CROSS JOIN `another table` AS v;

    Where I write ... above, you would need to write one additional line like the lines above it (beginning with WHEN), for each distinct group id, which I infer from your example corresponds to the additional columns of your second table.

    In general, SQL requires that table names and column names are fixed in the query at the time it is prepared. There’s no way to base an expression dynamically on the values read during query execution.

    You can use CASE as shown above as a kind of branching construct, so a different sub-expression is used depending on matching specific values.

    But this requires that you know all the values up front, so you can format the CASE expression. Each value must be listed explicitly.

    If you don’t know the values in advance, then you can’t do this in a single query. You can’t make a query that dynamically adds more terms to an expression depending on the values it reads during execution.

    Login or Signup to reply.
  2. 0
    Let’s create groups table:
    CREATE TABLE grps (
    id INT,
    name VARCHAR(50)
);

INSERT INTO grps (id, name) VALUES
(1, 'Group1'),
(2, 'Group2'),
(3, 'Group3');
    Let’s create group values table:
    CREATE TABLE group_values (
    id INT,
    group_1 INT,
    group_2 INT,
    group_3 INT
);

INSERT INTO group_values (id, group_1, group_2, group_3) VALUES
(1, 10, 8, 15),
(2, 12, 6, 20);

    Answer:

    WITH UnpivotedGroups AS (
    -- Unpivot the group_1 column
    SELECT 
        gv.id AS value_id, 
        g1.id AS group_id, 
        gv.group_1 AS value
    FROM group_values gv
    JOIN grps g1 ON g1.name = 'Group1'

    UNION ALL

    -- Unpivot the group_2 column
    SELECT 
        gv.id AS value_id, 
        g2.id AS group_id, 
        gv.group_2 AS value
    FROM group_values gv
    JOIN grps g2 ON g2.name = 'Group2'

    UNION ALL

    -- Unpivot the group_3 column
    SELECT 
        gv.id AS value_id, 
        g3.id AS group_id, 
        gv.group_3 AS value
    FROM group_values gv
    JOIN grps g3 ON g3.name = 'Group3'

    -- Add more UNION ALL clauses for additional groups here
    -- UNION ALL
    -- SELECT ...
)

-- Select from the CTE and add row numbers
SELECT 
    ROW_NUMBER() OVER (ORDER BY group_id, value_id) AS id,
    group_id,
    value
FROM UnpivotedGroups
ORDER BY group_id, id
;

    +----+----------+-------+
| id | group_id | value |
+----+----------+-------+
|  1 |        1 |    10 |
|  2 |        1 |    12 |
|  3 |        2 |     8 |
|  4 |        2 |     6 |
|  5 |        3 |    15 |
|  6 |        3 |    20 |
+----+----------+-------+
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search