Question: 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?

Answer: 
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](http://en.wikipedia.org/wiki/Data_Manipulation_Language) 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](http://www.sqlfiddle.com/#!2/13090/24).
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 `type`s 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

```