Join tables listing rows as columns joined to another table?

Question

I have 3 tables - things, defs, and info (terrible names, but significantly reduced for simplicity!)

info

CREATE TABLE `info` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `thingid` bigint(10) NOT NULL DEFAULT '0',
  `defid` bigint(10) NOT NULL DEFAULT '0',
  `data` longtext NOT NULL,
  PRIMARY KEY (`id`),
  KEY `infodata_coufie_ix` (`thingid`,`defid`)
);

id   | thingid  | defid   | data
1    | 1        | 1       | 1
1    | 1        | 2       | 25
1    | 2        | 1       | 0
1    | 2        | 3       | yellow
1    | 3        | 1       | 0

defs

CREATE TABLE `defs` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `datatype` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
);

id   | name    | datatype
1    | enabled | boolean
2    | size    | numeric
3    | colour  | string

things

CREATE TABLE `things` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
);

id    | name
1     | bill
2     | terry
3     | nancy

I'd like to be able to show the "defs"' value of "things", so a resultant table / view would be something like

thingid | name   | enabled   | size   | colour
1       | bill   | true      | 25     | null
2       | terry  | false     | null   | yellow
3       | nancy  | true      | null   | null

so the rows from defs would become column headers; the values matching those column headers for thingid would form the data for those rows.

I've done this long ago in SQL Server, and for the life of me can't remember how to do it. I now need to do it in MySql5. I've been reading up and down http://www.artfulsoftware.com/infotree/queries.php and various SE articles but I've now confused the heck out of myself, so I have to actually ask someone.

Answer 1

My solution:

CREATE TABLE info
    (`id` int, `thingid` int, `defid` int, `data` varchar(6))
;

INSERT INTO info
    (`id`, `thingid`, `defid`, `data`)
VALUES
    (1, 1, 1, '1'),
    (1, 1, 2, '25'),
    (1, 2, 1, '0'),
    (1, 2, 3, 'yellow'),
    (1, 3, 1, '0'),
    (1, 1, 5, 'bad'),
    (1, 1, 6, 2606),
    (1, 3, 4, 'banana')
;

CREATE TABLE defs
    (`id` int, `name` varchar(7), `datatype` varchar(7))
;

INSERT INTO defs
    (`id`, `name`, `datatype`)
VALUES
    (1, 'enabled', 'boolean'),
    (2, 'size', 'numeric'),
    (3, 'colour', 'string'),
    (4, 'flavour', 'thing'),
    (5, 'smell', 'essence'),
    (6, 'line', 'numeric')
;

CREATE TABLE things
    (`id` int, `name` varchar(5))
;

INSERT INTO things
    (`id`, `name`)
VALUES
    (1, 'bill'),
    (2, 'terry'),
    (3, 'nancy')
;

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      ' MAX(IF(d.name = ''',
      d.name,
      ''', i.data, NULL)) AS ',
      d.name
    )
  ) INTO @sql
FROM info i join defs d on i.defid = d.id;
SET @sql = CONCAT('SELECT i.thingid, t.name, ',
           @sql, 
          ' FROM info i',
          ' JOIN defs d ON i.defid = d.id',
          ' JOIN things t ON i.thingid = t.id',
          ' GROUP BY i.thingid');
PREPARE statement FROM @sql;
EXECUTE statement;

See fiddle: http://www.sqlfiddle.com/#!2/d481a/2

As you add more columns to defs, and subsequent data into info for those records, more columns appear on the results, with nulls in non matching columns as they should be.

Answer 2

SELECT i.thingid, t.name,
       MAX(IF(d.name = "enabled", i.data, NULL)) enabled,
       MAX(IF(d.name = "size", i.data, NULL)) size,
       MAX(IF(d.name = "colour", i.data, NULL)) colour
FROM info i
JOIN defs d ON i.defid = d.id
JOIN things t ON i.thingid = t.id
GROUP BY i.thingid

DEMO