3

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
rfpdl
  • 956
  • 1
  • 11
  • 35

1 Answers1

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).

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 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\ – rfpdl Dec 08 '14 at 06:52