sqldatabasepivotcreate-view

SQL - How do I create a view that pivots and existing table

I have a Table for Our Dynamic fields on user created Forms:

The Table is Formatted like this:

ID  | FIELD NAME 
-------------------
1   | Gross Gallons

In order to form Joins with other tables I need to create a view that looks like this

ID_1 
------------
Gross Gallons

Any help would be appreciated

Thanks

Solution

  • This is a simple approach to make dynamic fields.

    Given table:

    mysql> select * from dyna_field;

+------+---------------+
| id   | field_name    |
+------+---------------+
|    1 | Gross Gallons |
|    2 | Net Gallons   |
|    3 | Total         |
+------+---------------+

    Make dynamic query:

    select 
  group_concat(concat('(case when id = ', id, ' then field_name else "" end) as ID_', id, ' ')) into @sql_case
from dyna_field 
join(SELECT @sql_case:='') c;

set @sql_case = concat('select ', @sql_case, ', 1 as uid from dyna_field;');

prepare statement FROM @sql;

execute statement;

+---------------+-------------+-------+-----+
| ID_1          | ID_2        | ID_3  | uid |
+---------------+-------------+-------+-----+
| Gross Gallons |             |       |   1 |
|               | Net Gallons |       |   1 |
|               |             | Total |   1 |
+---------------+-------------+-------+-----+