Dynamic Pivot table columns mysql

Question

I have following tables

demographic_categories

demographic_id  demographic_name
1               color
2               age_group

project_tests

test_id  group_id  project_id
1        1         1
2        1         1

test_demographic_requirements

test_id  project_id  demgraphic_id  demographic_value
1        1           1              blue
1        1           2              young
2        1           1              green
2        1           2              middle

And I need a query which would give me following result :

test_id  group_id  color  age_group 
1        1         blue   young
2        1         green  middle

I guess we need to use concept of pivot table to get the result, but I am unable to. And I demographic categories can we might be same they tend to change so I need something dynamic so what would be the best way to do it ?

I have tried doing the following based of previous similar questions but it doesn't seems to work for me, here is what I have tried:

SET @sql = NULL;
    SELECT
      GROUP_CONCAT(DISTINCT
        CONCAT(
          'max(case when dc.demographic_name = ''',
          demographic_name,
          ''' then trd.demographic_value end) AS ',
          replace(demographic_name, ' ', '')
        )
      ) INTO @sql
    from demographic_categories;

    SET @sql = CONCAT('SELECT pt.test_id, pt.group_id,
    ', @sql,'
    from test_requirement_demographic trd
    LEFT JOIN demographic_categories dc ON trd.demographic_id = dc.demographic_id
    LEFT JOIN project_tests pt ON pt.test_id = trd.test_id and project_id =1
    group by pt.test_id;');

    PREPARE stmt FROM @sql;
    EXECUTE stmt;

    DEALLOCATE PREPARE stmt;

But why are you so determined to do this in the RDBMS?

Strawberry
– Strawberry

2018-05-30 05:53:44 +00:00
Commented May 30, 2018 at 5:53 — Strawberry
– Strawberry, Commented May 30, 2018 at 5:53
See mysql.rjweb.org/doc.php/pivot for dynamic code.

Rick James
– Rick James

2018-06-01 17:34:41 +00:00
Commented Jun 1, 2018 at 17:34 — Rick James
– Rick James, Commented Jun 1, 2018 at 17:34

Udit Solanki · Accepted Answer · 2018-05-30 05:03:24Z

3

Your dynamic SQL is just fine except for one thing. It has an ambiguous column project_id on your second left join, Just replace it with pt.project_id or trd.project_id and it will give desired result.

SET @sql = NULL;
    SELECT
      GROUP_CONCAT(DISTINCT
        CONCAT(
          'max(case when dc.demographic_name = ''',
          demographic_name,
          ''' then trd.demographic_value end) AS ',
          replace(demographic_name, ' ', '')
        )
      ) INTO @sql
    from demographic_categories;

SET @sql = CONCAT('SELECT pt.test_id, pt.group_id,
', @sql,'
from test_demographic_requirements trd
LEFT JOIN demographic_categories dc ON trd.demographic_id = dc.demographic_id
LEFT JOIN project_tests pt ON pt.test_id = trd.test_id and pt.project_id =1
group by pt.test_id;');

PREPARE stmt FROM @sql;
EXECUTE stmt;

DEALLOCATE PREPARE stmt;

I ran it on a rextester. Here is the link : dynamic_pivot_test

answered May 30, 2018 at 5:03

Udit Solanki

5315 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

akhildn Over a year ago

i tried in my workbench and it shows up this error : 09:35:58 PREPARE stmt FROM @sql Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1 0.000 sec

Udit Solanki Over a year ago

Is query you posted in your question running successfully?

Collectives™ on Stack Overflow

Dynamic Pivot table columns mysql

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related