Using MySQL GROUP_CONCAT or PIVOT on multiple joined tables

Question

ISSUE

I'm working on a content management system for a school, and I'd like to be able to create a view of multiple joined tables. In addition to joining the tables, I need to perform a PIVOT or GROUP_CONCAT (or some other function) in order to take multiple rows from each table and use them as columns in the view. To help visualize, here is a simplification of what I'm working with:

SETUP

STRUCTURE:

DROP TABLE IF EXISTS `date`;
CREATE TABLE `date` (
  `col_pkid` int(11) NOT NULL AUTO_INCREMENT,
  `col_guid` varchar(100) NOT NULL DEFAULT '',
  `col_entry` varchar(100) NOT NULL,
  `col_inquiry_name` varchar(100) NOT NULL,
  `col_value` int(11) DEFAULT NULL,
  PRIMARY KEY (`col_pkid`)
);

DROP TABLE IF EXISTS `bigText`;
CREATE TABLE `bigText` (
  `col_pkid` int(11) NOT NULL AUTO_INCREMENT,
  `col_guid` varchar(100) NOT NULL DEFAULT '',
  `col_entry` varchar(100) NOT NULL,
  `col_inquiry_name` varchar(100) NOT NULL,
  `col_value` text,
  PRIMARY KEY (`col_pkid`)
);

DROP TABLE IF EXISTS `smallText`;
CREATE TABLE `smallText` (
  `col_pkid` int(11) NOT NULL AUTO_INCREMENT,
  `col_guid` varchar(100) NOT NULL,
  `col_entry` varchar(100) NOT NULL,
  `col_inquiry_name` varchar(100) NOT NULL,
  `col_value` varchar(100) DEFAULT '',
  PRIMARY KEY (`col_pkid`)
);

DATA:

INSERT INTO `date` (`col_pkid`, `col_guid`, `col_entry`, `col_inquiry_name`, `col_value`)
VALUES
    (1,'d2kih1ho5z','1','Semester',1294834220),
    (2,'d2kih1j99y','2','Semester',1327077210);

INSERT INTO `bigText` (`col_pkid`, `col_guid`, `col_entry`, `col_inquiry_name`, `col_value`)
VALUES
    (16,'d2kih1kxwh','1','Description','Lorem ipsum dolor sit amet...'),
    (17,'d2kih1mhnn','2','Description','Consectetur adipiscing elit...');

INSERT INTO `smallText` (`col_pkid`, `col_guid`, `col_entry`, `col_inquiry_name`, `col_value`)
VALUES
    (1,'d2kih1njuj','1','Title','Addition and Subtraction'),
    (2,'d2kih1ot0x','1','Course','Math'),
    (16,'d2kj5osy71','1','Location','Building 7'),
    (17,'d2kj5p0z8f','1','Teachers','John'),
    (18,'d2kj5p5efi','1','Teachers','Jane'),
    (19,'d2kj5p98xv','1','Teachers',''),
    (20,'d2kj5pasi9','1','Teachers',''),
    (21,'d2kj5pbqbg','1','Teachers',''),
    (22,'d2kj5pdxdb','2','Title','Reading Mark Twain'),
    (23,'d2kih198do','2','Course','Literature'),
    (24,'d2kj5opcgo','2','Location','Building 4'),
    (25,'d2hd00n5eb','2','Teachers','Billy'),
    (26,'d2hea3jo74','2','Teachers','Bob'),
    (27,'d2hec78m5e','2','Teachers',''),
    (28,'d2hec7bnar','2','Teachers',''),
    (29,'d2hec7cbrq','2','Teachers','');

SOME FAILED ATTEMPTS

I am able to join the tables together reasonably well by doing the following:

DROP VIEW IF EXISTS entries;
CREATE VIEW entries AS
SELECT
    st.col_entry,
    if(st.col_inquiry_name = 'Title', st.col_value, NULL) AS 'Title', 
    if(st.col_inquiry_name = 'Course', st.col_value, NULL) AS 'Course',
    if(bt.col_inquiry_name = 'Description', bt.col_value, NULL) AS 'Description',
    if(d.col_inquiry_name = 'Semester', d.col_value, NULL) AS 'Semester',
    if(st.col_inquiry_name = 'Location', st.col_value, NULL) AS 'Location',
    if(st.col_inquiry_name = 'Teachers', st.col_value, NULL) AS 'Teachers'
FROM smallText st
INNER JOIN bigText bt
ON st.col_entry = bt.col_entry
INNER JOIN date d
ON bt.col_entry = d.col_entry
GROUP BY col_entry;

but here I'm only getting the value of the first row from each table. This is why I tried introducing GROUP_CONCAT although I haven't had much success with that either:

DROP VIEW IF EXISTS entries;
CREATE VIEW entries AS
SELECT
    st.col_entry,
    GROUP_CONCAT(if(st.col_inquiry_name = 'Title', st.col_value, NULL)) AS 'Title', 
    GROUP_CONCAT(if(st.col_inquiry_name = 'Course', st.col_value, NULL)) AS 'Course',
    GROUP_CONCAT(if(bt.col_inquiry_name = 'Description', bt.col_value, NULL)) AS 'Description',
    GROUP_CONCAT(if(d.col_inquiry_name = 'Semester', d.col_value, NULL)) AS 'Semester',
    GROUP_CONCAT(if(st.col_inquiry_name = 'Location', st.col_value, NULL)) AS 'Location',
    GROUP_CONCAT(if(st.col_inquiry_name = 'Teachers', st.col_value, NULL)) AS 'Teachers'
FROM smallText st
INNER JOIN bigText bt
ON st.col_entry = bt.col_entry
INNER JOIN date d
ON bt.col_entry = d.col_entry
GROUP BY col_entry;

I'm able to get each column in the view populated with the corresponding values from the tables, but the date and bigText columns are being duplicated according to the orignal row count that they are joined on (7 times).

(Also, as another added level of complexity... I'd like to have it so that the different Teachers would find their way into their own columns as well: Teacher_01, Teacher_02, .. Teacher_05, and I'd guess this probably involves a significant restructuring of what I've got.)

Thanks!

...UPDATE

So, I was able to produce a query which outputs something a bit closer to what I'm looking for, but I can't use it in a view because there is a subquery in the FROM clause:

SELECT
st.col_entry,
Title,
Course,
Description,
Semester,
Location,
Teachers
FROM smallText st
JOIN (SELECT smallText.col_entry, GROUP_CONCAT(if(smallText.col_inquiry_name = 'Title', smallText.col_value, NULL)) AS 'Title' 
    FROM smallText GROUP BY smallText.col_entry) a
ON a.col_entry = st.col_entry
JOIN (SELECT smallText.col_entry, GROUP_CONCAT(if(smallText.col_inquiry_name = 'Course', smallText.col_value, NULL)) AS 'Course'
    FROM smallText GROUP BY smallText.col_entry) b
ON b.col_entry = st.col_entry
JOIN (SELECT bigText.col_entry, GROUP_CONCAT(if(bigText.col_inquiry_name = 'Description', bigText.col_value, NULL)) AS 'Description'
    FROM bigText GROUP BY bigText.col_entry) c
ON c.col_entry = st.col_entry
JOIN (SELECT date.col_entry, GROUP_CONCAT(if(date.col_inquiry_name = 'Semester', date.col_value, NULL)) AS 'Semester'
    FROM date GROUP BY date.col_entry) d
ON d.col_entry = st.col_entry
JOIN (SELECT smallText.col_entry, GROUP_CONCAT(if(smallText.col_inquiry_name = 'Location', smallText.col_value, NULL)) AS 'Location'
    FROM smallText GROUP BY smallText.col_entry) e
ON e.col_entry = st.col_entry
JOIN (SELECT smallText.col_entry, GROUP_CONCAT(if(smallText.col_inquiry_name = 'Teachers', smallText.col_value, NULL)) AS 'Teachers'
    FROM smallText GROUP BY smallText.col_entry) f
ON f.col_entry = st.col_entry
GROUP BY st.col_entry

THE RESULT

The output of this query looks like this:

Entry   Title                           Course      Description                     Semester    Location    Teachers
---------------------------------------------------------------------------------------------------------------------------------
1       Addition and Subtraction        Math        Lorem ipsum dolor sit amet...   1294834220  Building 7  NULL
2       Reading Mark Twain              Literature  Consectetur adipiscing elit...  1327077210  Building 4  NULL

which, with the exception of the null Teachers column, is basically what I'm looking for (but generated in some way that I can use the query in a view).

Additionally, as I mentioned above, I'd like to be able to pull multiple values for the same inquiry_name into individual columns. So the final desired output would look like:

THE DESIRED RESULT

Entry   Title                           Course      Description                     Semester    Location    Teachers_01 Teachers_02 Teachers_03 Teachers_04 Teachers_05
---------------------------------------------------------------------------------------------------------------------------------
1       Addition and Subtraction        Math        Lorem ipsum dolor sit amet...   1294834220  Building 7  John        Jane        NULL        NULL        NULL
2       Reading Mark Twain              Literature  Consectetur adipiscing elit...  1327077210  Building 4  Billy       Bob         NULL        NULL        NULL

Thanks!

great job posting sample data and the table structures (as well as what you have tried), but can you include a sample of the desired result? — Taryn, Sep 18 '12 at 10:46

Taryn · Accepted Answer · 2012-09-18T23:14:41.393

Here is a mock-PIVOT applying a rownum to the teachers:

select s.col_entry,
  max(case when s.col_inquiry_name = 'Title' then s.col_value end) AS 'Title',
  max(case when s.col_inquiry_name = 'Course' then s.col_value end) AS 'Course',
  max(case when b.col_inquiry_name = 'Description' then b.col_value end) AS 'Description',
  max(case when d.col_inquiry_name = 'Semester' then d.col_value end) AS 'Semester',
  max(case when s.col_inquiry_name = 'Location' then s.col_value end) AS 'Location',
  max(case when tch.grp = 'Teachers_01' then tch.col_value end) AS 'Teachers_01',
  max(case when tch.grp = 'Teachers_02' then tch.col_value end) AS 'Teachers_02',
  max(case when tch.grp = 'Teachers_03' then tch.col_value end) AS 'Teachers_03',
  max(case when tch.grp = 'Teachers_04' then tch.col_value end) AS 'Teachers_04',
  max(case when tch.grp = 'Teachers_05' then tch.col_value end) AS 'Teachers_05'
from smallText s
left join bigText b
  on s.col_entry = b.col_entry 
left join date d
  on b.col_entry = d.col_entry 
left join
(
  select col_entry, col_value, concat('Teachers_0', group_row_number) grp
  from
  (
    select col_entry, col_value,
      @num := if(@col_entry = `col_entry`, @num + 1, 1) as group_row_number,
      @col_entry := `col_entry` as dummy
    from smallText , (SELECT @rn:=0) r
    where col_inquiry_name = 'Teachers'
      and col_value != ''
  ) x
) tch
  on s.col_entry = tch.col_entry
group by s.col_entry;

see SQL Fiddle with demo

Edit #1, based on the additional field you provided col_order you can use it to determine the teachers without using rownum variables:

select s.col_entry,
  max(case when s.col_inquiry_name = 'Title' then s.col_value end) AS 'Title',
  max(case when s.col_inquiry_name = 'Course' then s.col_value end) AS 'Course',
  max(case when b.col_inquiry_name = 'Description' then b.col_value end) AS 'Description',
  max(case when d.col_inquiry_name = 'Semester' then d.col_value end) AS 'Semester',
  max(case when s.col_inquiry_name = 'Location' then s.col_value end) AS 'Location',
  max(case when s.col_inquiry_name = 'Teachers'
        and s.col_order = 1 then s.col_value end) AS 'Teachers_01',
  max(case when s.col_inquiry_name = 'Teachers'
        and s.col_order = 2 then s.col_value end) AS 'Teachers_02',
  max(case when s.col_inquiry_name = 'Teachers'
        and s.col_order = 3 then s.col_value end) AS 'Teachers_03',
  max(case when s.col_inquiry_name = 'Teachers'
        and s.col_order = 4 then s.col_value end) AS 'Teachers_04',
  max(case when s.col_inquiry_name = 'Teachers'
        and s.col_order = 5 then s.col_value end) AS 'Teachers_05'
from smallText s
left join bigText b
  on s.col_entry = b.col_entry 
left join date d
  on b.col_entry = d.col_entry 
group by s.col_entry

see SQL Fiddle with Demo

Wow, this is great. Now, is it possible to do this in a `VIEW`? At the moment there is a `SELECT` in the final `JOIN`'s `FROM` clause, preventing this from being usable in a view. — adekom, Sep 18 '12 at 17:08
Well, I need to use a view rather than a prepared statement because this is part of a content management system. I'm trying to reformat the data which is spread out in multiple tables into a single view so that it is easily accessible in a more basic single-row-per-entry format and so that the front-end designer can just pull data from that view really easily. — adekom, Sep 18 '12 at 18:02
@user890857 a few issues, I know you can't use subqueries in views and I don't think you can use a variable in a MySQL view. I don't have time to try to rewrite this right now but a suggestion (albeit not great one) is to update the teacher `col_value` to say `Teacher_01`, etc. Then you will not need a `rownum` variable- see [SQL Fiddle](http://sqlfiddle.com/#!2/24e27/3) — Taryn, Sep 18 '12 at 18:37
Thanks for helping me on this. I see how changing the `inquiry_name` could make this easier, but other parts of the system work better if that name remains the same for each of the rows. Each table includes a row called `col_order`, and I've updated the [sql fiddle](http://sqlfiddle.com/#!2/f796f/1) to show how that works. I'm not sure if there is a way to use that column as a means for generating the individual teacher columns. — adekom, Sep 18 '12 at 22:43
@user890857 please see my edit, the `col_order` table can be used to get the `rownum` — Taryn, Sep 18 '12 at 23:15

Using MySQL GROUP_CONCAT or PIVOT on multiple joined tables

1 Answers1

see SQL Fiddle with demo

see SQL Fiddle with Demo

Linked