vb.net - Advanced SQL Select and Union Statements -

i've seen other similar questions , have tried implementing many solutions, to no avail far. specific questions involves little more complexity. need create table , join columns right side depending on criterion. seems simple enough, there few bumps encountering.

the tables follows:

adc_data_collection_header  (pk)transaction_id | bemsid | device | timestamp | config_name  adc_data_collection_apps  (fk)config_name | num_data_elements | data_element1 | data_element2 | data_element3 | data_element4   adc_data_collection_data  (fk)transaction_id | data_element_number | data 

i want final output like:

transaction_id | device | config_name | data | data | data | data 

the "data" column filled in using table adc_data_collection_data. first instance of "data" "data" field in adc_data_collection_data data_element_number = 1. second instance of "data" "data" field in adc_data_collection_data data_element_number = 2... , on.

the furthest have gotten using join statement, except have nulls in places not want them. code have used , results posted below. far wrote code first 2 columns of data.

select  adc_data_collection_header.bemsid,  adc_data_collection_header.device,  adc_data_collection_header.config_name,  null locationlabel,  null partno  /*null partno2,  null de4,  null de5,  null de6 */  adc_data_collection_header,  adc_data_collection_apps,  adc_data_collection_data   adc_data_collection_header.config_name = 'mobilescanning'  , adc_data_collection_header.bemsid = '2386531'  , adc_data_collection_header.config_name = adc_data_collection_apps.config_name  , (to_date('7/19/2013','mm/dd/yyyy') <= timestamp , to_date('7/27/2013','mm/dd/yyyy') >= timestamp)  , adc_data_collection_header.transaction_id = adc_data_collection_data.transaction_id  union select null bemsid,  null device,  null config_name, adc_data_collection_data.data locationlabel,  null partno  adc_data_collection_data, adc_data_collection_header,  adc_data_collection_apps  adc_data_collection_data.data_element_number = 3  , adc_data_collection_header.config_name = 'mobilescanning'  , (to_date('7/19/2013','mm/dd/yyyy') <= timestamp , to_date('7/27/2013','mm/dd/yyyy') >= timestamp)  , adc_data_collection_header.transaction_id = adc_data_collection_data.transaction_id  union select null bemsid,  null device,  null config_name, null locationlabel, adc_data_collection_data.data partno  adc_data_collection_data, adc_data_collection_header,  adc_data_collection_apps  adc_data_collection_data.data_element_number = 4  , adc_data_collection_header.config_name = 'mobilescanning'  , (to_date('7/19/2013','mm/dd/yyyy') <= timestamp , to_date('7/27/2013','mm/dd/yyyy') >= timestamp)  , adc_data_collection_header.transaction_id = adc_data_collection_data.transaction_id 

the result appears null values not want have.

if can offer explicit solution using join statement or fix union approach, appreciated. thank in advance!

i use pivot this:

select     h.transaction_id,     h.device,     h.config_name,     d.data1,     d.data2,     d.data3,     d.data4     adc_data_collection_header h     inner join (       select *       adc_data_collection_data       pivot       (           max(data)           data_element_number in (1 data1, 2 data2, 3 data3, 4 data4)       )     ) d         on d.transaction_id = h.transaction_id     (to_date('7/19/2013','mm/dd/yyyy') <= timestamp , to_date('7/27/2013','mm/dd/yyyy') >= timestamp); 

i put example sql fiddle at: http://www.sqlfiddle.com/#!4/fe1c94/9/0