CODEWITHSUNDEEP
sqlsql-server
steven
steven

Reputation: 269

Consolidating results on one row SQL

I would like to consolidate a one to many relationship that outputs on different rows to a single row.

(select rate_value1 
      FROM xgenca_enquiry_event 
       INNER JOIN  xgenca_enquiry_iso_code_translation 
             ON 
       xgenca_enquiry_event_rate.rate_code_id 
           = xgenca_enquiry_iso_code_translation.id  
       where xgenca_enquiry_event_rate.event_id = xgenca_enquiry_event.id 
              and ISO_code = 'PDIV') as PDIVrate, 
(select rate_value1 
       FROM xgenca_enquiry_event 
        INNER JOIN xgenca_enquiry_iso_code_translation 
              ON 
         xgenca_enquiry_event_rate.rate_code_id 
           = xgenca_enquiry_iso_code_translation.id  
        where xgenca_enquiry_event_rate.event_id = xgenca_enquiry_event.id 
              and ISO_code = 'TAXR') as TAXrate

PDIVrate    TAXrate
NULL        10.0000000
0.0059120   NULL

I would like the results on one row. Any help would be greatly appreciated. Thanks.

Upvotes: 1

Views: 538

Answers (2)

Art
Art

Reputation: 5782

Look here: Can I Comma Delimit Multiple Rows Into One Column?

Simulate Oracle's LISTAGG() in SQL Server using STUFF:

SELECT Column1,
  stuff((
   SELECT ', ' + Column2
     FROM tableName as t1
      where t1.Column1 = t2.Column1
       FOR XML PATH('')
     ), 1, 2, '')
 FROM tableName as t2
GROUP BY Column1
/

Copied from here: https://github.com/jOOQ/jOOQ/issues/1277

Upvotes: 0

Taryn
Taryn

Reputation: 247670

You can use an aggregate function to perform this:

select 
  max(case when ISO_code = 'PDIV' then rate_value1 end) PDIVRate,
  max(case when ISO_code = 'TAXR' then rate_value1 end) TAXRate
FROM xgenca_enquiry_event_rate r 
INNER JOIN  xgenca_enquiry_iso_code_translation t
  ON r.rate_code_id = t.id  
INNER JOIN xgenca_enquiry_event e
  ON r.event_id = e.id

It looks like you are joining three tables are are identical in the queries. This consolidates this into a single query using joins.

Upvotes: 2

Related Questions