samedi 25 avril 2015

SQL for showing row data in columns


Here is my sql (in mysql table)

select * from(SELECT sample_register.usin,    
                     DATE_FORMAT(sample_register.doc,'%d-%m-%Y') as doc1,    
                     sample_register.location,    
                     sample_register.description,    
                     sample_register.type,    
                     sample_allocation.gamma,    
                     gamma_results.act,    
                     gamma_results.act_sd,    
                     gamma_results.mdl,    
                     gamma_results.bdl,    
                     DATE_FORMAT(count_dt,'%d-%m-%Y') as count_dt    
               FROM sample_register    
               LEFT JOIN sample_allocation    
               ON sample_register.usin=sample_allocation.usin    
               LEFT JOIN gamma_results    
               ON gamma_results.usin = sample_register.usin    
               AND gamma_results.istp='Cs137'    
               WHERE mid(sample_register.usin,3,1)='F'    
               AND sample_register.doc BETWEEN '2015-01-01'    
                                       AND '2015-03-31'    
                                       AND sample_register.category='ter'    
                                       AND sample_allocation.gamma='Y'    
               ORDER BY mid(sample_register.usin,3,1),    
                        sample_register.doc,    
                        sample_register.usin) AS a    
               LEFT JOIN (SELECT sample_register.usin,    
                                 gamma_results.act,    
                                 gamma_results.act_sd,    
                                 gamma_results.mdl,    
                                 gamma_results.bdl    
                          FROM sample_register    
                          LEFT JOIN gamma_results    
                          ON gamma_results.usin = sample_register.usin    
                          AND gamma_results.istp='k40'    
                          WHERE mid(sample_register.usin,3,1)='F'    
                          AND sample_register.doc    
                          BETWEEN '2015-01-01'    
                          AND '2015-03-31'    
                          AND (sample_register.category='ter')    
                          ORDER BY mid(sample_register.usin,3,1),    
                                   sample_register.doc,    
                                   sample_register.usin) AS b    
               ON a.usin=b.usin

There are 4 records in the gamma_results table. two records each for 10/04/2015 and 18/04/2015.

USIN        istp     act     count_dt
-----------------------------------------
15FML002    Cs137   0.00769  10/04/15
15FML002    K40     0        10/04/15
15FML002    Cs137   0.00608  18/04/15
15FML002    K40     12.117   18/04/15

Query output data in the following form (some fields I deleted for convenience)

15FML002            0.00769 Y   10/04/15    00
15FML002            0.00769 Y   10/04/15    12.117
15FML002            0.00608 Y   18/04/15    00
15FML002            0.00608 Y   18/04/15    12.117

But I want to get output in two records. That is like this

15FML002            0.00769 Y   10/04/15    00
15FML002            0.00608 Y   18/04/15    12.117

How can I reframe (join or union) the query to get output like this?


Aucun commentaire:

Enregistrer un commentaire