mysql select query get data from row based

Question

I have 4 tables:

source
fields(fields for the source)
source_data
source_data_details(child of source_data, containing records saved in row technique)

enter image description here

source
id    name    status
1     web     active


field
id   source_id    name      config    status
1    1            record_id 101       active
2    1            firstname 101       active
3    1            surname   101       active

source_data
id   source_id    status
1    1            active
2    1            active

source_data_details
id    source_data_id    source_field_id    value
1     1                 1                  1avhh2
2     1                 2                  john
3     1                 3                  mavrick
4     2                 1                  87k3jo
5     2                 2                  peter
6     2                 3                  lyne

How can I query it to make the result

source_data.id           record_id    firstname    surname
1                        1avhh2       john         mavrick
2                        87k3jo       peter        lyne

This question looks similar: stackoverflow.com/questions/7674786/mysql-pivot-table — Gábor Bakos
– Gábor Bakos, Commented Dec 7, 2014 at 12:52

Gordon Linoff · Accepted Answer · 2014-12-07 13:21:21Z

1

You can use multiple joins or aggregation. Here is an example using aggregation:

select sdd.source_data_id, 
       max(case when f.name = 'record_id' then sdd.value end) as record_id,
       max(case when f.name = 'firstname' then sdd.value end) as firstname,
       max(case when f.name = 'surname' then sdd.value end) as surname
from source_data_details sdd join
     field f
     on sdd.field_id = f.id
group by sdd.source_data_id;

Note that you have to explicitly put in every column you want in the output. If you want variable columns then you need to use dynamic SQL (prepare/execute statements).

answered Dec 7, 2014 at 13:21

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

rfpdl Over a year ago

I have to make it dynamic based on the field table.let us say that I am getting the data of a source with particular fields and getting the source_data_details for it\

Collectives™ on Stack Overflow

mysql select query get data from row based

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related