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.

This question is asked multiple times a day. Search for other questions with the tags `[mysql] [pivot]` and you'll find many answers. — Barmar, Dec 17 '13 at 00:48
You can't do it like you do in SQL-Server, because MySQL doesn't have a built-in PIVOT operator. — Barmar, Dec 17 '13 at 00:48
Pivot! Know the right search term. I was starting to go down the "just do two queries; build the second from the result of the first" pathway, which is roughly what this http://stackoverflow.com/a/12599372/1238884 kind of answer is doing anyway. I don't fondly recall MSSQL often, perhaps this is one of those times. — frumbert, Dec 17 '13 at 03:38
Nope, I'm lost again. I'm trying to start by pivoting `select info.dataid, defs.name, info.data from defs inner join info on defs.id = info.fieldid` so that each `info.dataid` gets columns `defs.name` populated with `info.data`. I get that once I have my pivot I will just inner join it to `things`. @Barmar I recognise that this is asked a lot, I've searched for hours, I've read more than 50 articles on dozens of sites, and I still don't understand it. — frumbert, Dec 17 '13 at 04:40

score 2 · Answer 1 · answered Dec 17 '13 at 04:47

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

answered Dec 17 '13 at 04:47

Barmar

741,623
53
500
612

other than size and colour always being null on my machine (but ok on sqlfiddle? 5.5.29), I now just have to make it dynamic since I don't know in advance how many defs there might be. Since I posted my last comment, I've read through that here: http://buysql.com/mysql/14-how-to-automate-pivot-tables.html and http://www.boynux.com/creating-pivot-reports-in-mysql/ and can see now, with your kind answer, how that would begin to be applied to my situation. What a PITA day! – frumbert Dec 17 '13 at 11:03
I was originally getting null colors because I left out the `u` in `"colour"`. Make sure you're consistent about it. Of course, if you use the dynamic SQL solutions that are necessary to handle arbirary defs, it will automatically match them up properly (unless you do something really silly like have both `color` and `colour` in the `defs` table). – Barmar Dec 17 '13 at 17:56

frumbert · Answer 2 · 2013-12-17T12:53:30.817

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.

Join tables listing rows as columns joined to another table?

2 Answers2

Linked