MySQL "Multi-Dimensional?" Dynamic Pivot

Question

I admit, I'm in over my head. I've gotten far enough that I can almost see light at the end of the tunnel, and yet, I'm not sure the next step to take.

I have created an SQLfiddle example here

Here's the SQL Pivot:

SET @sql = NULL;

SELECT
  GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(qrv.req_name = ''',qrv.req_name,''', qrv.req_value, NULL)) AS `',qrv.req_name,'`')) INTO @sql
FROM (SELECT qrt.req_name, qrv.id, qrv.req_value FROM qual_requirment_values qrv JOIN qual_requirment_types qrt ON qrt.id = qrv.req_type_id) qrv;

SET @sql = CONCAT('SELECT r.id, r.rank_name, 
                     ', @sql, ' 
                   FROM qual_rank_requirments qrr
                   LEFT JOIN (
                              SELECT qrt.req_name, qrv.id, qrv.req_value 
                                 FROM qual_requirment_values qrv 
                                JOIN qual_requirment_types qrt ON qrt.id = qrv.req_type_id
                             ) AS qrv  ON qrv.id = qrr.req_values_id                   
                   JOIN ranks r ON r.id = qrr.rank_id
                   GROUP BY qrv.id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Here's the Data Structures:

create table qual_rank_requirments
(
  id int,
  rank_id int,
  req_values_id int
);

insert into qual_rank_requirments values
(1, 4, 1),
(2, 4, 2),
(3, 5, 3),
(4, 5, 4),
(5, 6, 3),
(6, 6, 5),
(7, 7, 3),
(8, 7, 6),
(9, 8, 3),
(10, 8, 7);

create table qual_requirment_values
(
  id int,
  req_type_id int,
  req_value int
);

insert into qual_requirment_values values
(1, 1, 55),
(2, 3, 1100),
(3, 1, 110),
(4, 4, 2530),
(5, 5, 4950),
(6, 6, 14630),
(7, 6, 19800);

create table qual_requirment_types
(
  id int,
  req_name varchar(50)
);

insert into qual_requirment_types values
(1, 'pv'),
(2, 'psv'),
(3, 'tv4'),
(4, 'tv5'),
(5, 'tv6'),
(6, 'tv7');

create table ranks
(
  id int,
  rank_name varchar(50)
);

insert into ranks values
(4, 'gyv1'),
(5, 'gyv2'),
(6, 'gyv3'),
(7, 'gyv4'),
(8, 'yns1');

Here's what I am getting:

id  rank_name   pv      tv4     tv5     tv6     tv7
4   gyv1        55      (null)  (null)  (null)  (null)
4   gyv1        (null)  1100    (null)  (null)  (null)
5   gyv2        110     (null)  (null)  (null)  (null)
5   gyv2        (null)  (null)  2530    (null)  (null)
6   gyv3        (null)  (null)  (null)  4950    (null)
7   gyv4        (null)  (null)  (null)  (null)  14630
8   yns1        (null)  (null)  (null)  (null)  19800

Here's what I'm shooting for:

id  rank_name   pv      tv4     tv5     tv6     tv7
4   gyv1        55      1100    (null)  (null)  (null)
5   gyv2        110     (null)  2530    (null)  (null)
6   gyv3        110     (null)  (null)  4950    (null)
7   gyv4        110     (null)  (null)  (null)  14630
8   yns1        110     (null)  (null)  (null)  19800

I credit the following sources for helping me get this far.

Thanks @strapro for his tutorial:

http://stratosprovatopoulos.com/web-development/mysql/pivot-table-with-dynamic-columns/

And specifically @Rockse's answer on Dynamic Pivots which ultimately led me to @strapro's tutorial:

MySQL pivot table

score 1 · Accepted Answer · answered Aug 19 '15 at 06:18

1

You are grouping on the wrong column,use

GROUP BY r.rank_name

FIDDLE

answered Aug 19 '15 at 06:18

Mihai

26,325
7
66
81

Silly me! You make me wanna cry... both for feeling stupid and for joy all at the same time! – RedSands Aug 19 '15 at 06:22
@Mel Heh,it happened to all of us. – Mihai Aug 19 '15 at 06:23
Side question: Is there a more efficient way to accomplish the same result? I've read that the concat chains are massively inefficient. – RedSands Aug 19 '15 at 14:38
1

@RedSands It seems pretty simple to me,one thing it might be missing is an index on rank_name and if you have many different values on `req_name` you might need to set `group_concat_max_len` to a higher value.In dynamic pivot you can`t avoid GROUP_CONCAT. – Mihai Aug 19 '15 at 15:24

MySQL "Multi-Dimensional?" Dynamic Pivot

1 Answers1

Linked