CODEWITHSUNDEEP
mysql
Dani
Dani

Reputation: 5908

create dynamically created columns from a table in mysql

i would like to create virtual columns that are dynamic where the values are being generated based on a join table.

i have the following table called types:

id, name
1, TypeA
2, TypeB

and i have a table called category

id, name, type
1, a, 1
2, b, 2

i would like to have a query that returns the following

category name, TypeA, TypeB
a, 1, 0
b, 0, 1

is this possible to do in mysql?

Upvotes: 0

Views: 3945

Answers (2)

vyegorov
vyegorov

Reputation: 22895

I'd outline several cases here.

  1. First and most straightforward is the following: 

    SELECT c.name AS "CatName",
       IF(typea.id IS NULL, 0, 1) AS "TypeA",
       IF(typeb.id IS NULL, 0, 1) AS "TypeB"
  FROM category c
  LEFT JOIN types typea ON c.type = typea.id AND typea.name = 'TypeA'
  LEFT JOIN types typeb ON c.type = typeb.id AND typeb.name = 'TypeB';

    But this requires manually mentioning all types in the query, which is apparently not what you're seeking for.

  2. It is possible to build SQL query and use it, this method assumes you're running queries from some script, that can grab output from the first query and use it as a new query. 

    SELECT concat('SELECT c.name AS "CatName",',
    group_concat(concat('IF(',lower(t.name),
      '.id IS NULL,0,1) AS "',t.name,'"')),
    ' FROM category c ',
    group_concat(concat('LEFT JOIN types ',
      lower(t.name),' ON c.type = ',lower(t.name),'.id AND ',
      lower(t.name),'.name = ''',t.name,'''') SEPARATOR ' '),
    ';')
  FROM types t;

    Writing a small shell (or other) script should be easy.

  3. In the standard SQL it is not possible to use contents of the tables to create DML statements. Different databases provide different facilities for this, like PIVOT statement, procedural languages, etc. I do not know how to achieve this with MySQL facilities, but the idea is to dynamically build a query outlined in point #2 and execute it.

I've covered first 2 cases on the SQL Fiddle.

Upvotes: 3

Zane Bien
Zane Bien

Reputation: 23125

There is a feature called PIVOT which does what you want, but unfortunately, it is not available in MySQL.

What you could do however, is concatenate all types into a single string per category:

SELECT
    a.name,
    GROUP_CONCAT(b.name) AS types
FROM
    category a
LEFT JOIN
    types b ON a.type = b.id
GROUP BY
    a.id

Which would result in something like:

name    |    types
--------------------------------
a       |    TypeA
b       |    TypeB
c       |    TypeA,TypeB,TypeC,TypeD

Where category c has four different types, but a and b only have one type associated with them.

If you know beforehand what and how many types you're going to check on, and want to display a boolean value if that type exists for the category, you could do this:

SELECT,
    a.name,
    b.id IS NOT NULL AS TypeA,
    c.id IS NOT NULL AS TypeB,
    -- etc...
FROM
    category a
LEFT JOIN
    types b ON a.type = b.id AND b.id = 1
LEFT JOIN
    types c ON a.type = c.id AND c.id = 2
-- etc...

Edit: If you don't know the number of columns you're going to create beforehand, but still want boolean values for each type in their own separate columns, another option would be to dynamically build the query string in your application logic. Let's say you were using PHP for example:

$columns = $ljoins = array();
$i = 1;

foreach($pdo->query('SELECT id, name FROM types') as $row)
{
    $columns[] = "t$i.id IS NOT NULL AS " . $row['name'];
    $ljoins[] = "LEFT JOIN types t$i ON a.type = t$i.id AND t$i.id = " . $row['id'];
    $i++;
}

$sql = 'SELECT a.name, ' . implode(', ', $columns) . ' FROM category a ' . implode(' ', $ljoins);
$stmt = $pdo->query($sql);

// Do stuff with result-set

Upvotes: 1

Related Questions