CODEWITHSUNDEEP
sqloracle-database
Alexander Hristov
Alexander Hristov

Reputation: 311

Oracle query to turn rows into columns

I'm trying to turn rows into columns, but the way I'm currently doing is very uneffective. I'd like to find a method to do this that's doesn't require me to turn every thing into a subquery. This is what I'm currently using:

select h.request_id, h.SAMPLES_DATE, h.SAMPLES_TIME, h.parent_area_name, h.area_name, h.point_name, h.oblastname,
unistr((select r.measured_result from lab_requests_requrements r where r.request_id=h.request_id and r.requirement_id = 51)) wr51,
unistr((select r.measured_result from lab_requests_requrements r where r.request_id=h.request_id and r.requirement_id = 131)) wr131,
unistr((select r.measured_result from lab_requests_requrements r where r.request_id=h.request_id and r.requirement_id = 143)) wr143
from lab_requests_head_view h
where h.water_type_id = 3 and h.water_type = 2
order by h.request_number

I've got more than 300 different requirements and I need to write a subquery for each one. Is there a way to just pivot these so it can be more dynamic?

Upvotes: 1

Views: 65

Answers (2)

Dmitriy
Dmitriy

Reputation: 5565

Is there a way to just pivot these so it can be more dynamic?

Yes, you can use Oracle PIVOT. It allows to make code shorter, but anyway you need to describe all your 300 columns.

select *
  from (select h.request_id, h.SAMPLES_DATE, h.SAMPLES_TIME, h.parent_area_name, 
               h.area_name, h.point_name, h.oblastname,r.requirement_id
          from lab_requests_head_view h
               left join lab_requests_requrements r on r.request_id = h.request_id
         where h.water_type_id = 3 and h.water_type = 2)
 pivot (max(r.measured_result) for requirement_id in (51, 131, 143))
 order by h.request_number

You have to write all desired requirement_id in the line for requirement_id in (...) and choose an aggregate function. I used max, because I don't know which fits best for you. If you have unique results in r.measured_result, it should be OK.

P. S. Pivot is available in Oracle 11.1 or later versions.

Upvotes: 1

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

You need a JOIN and CASE .

SELECT h.request_id,
         h.SAMPLES_DATE,
         h.SAMPLES_TIME,
         h.parent_area_name,
         h.area_name,
         h.point_name,
         h.oblastname,
         UNISTR (CASE WHEN r.request_id = 51 THEN r.request_id END) wr51,
         UNISTR (CASE WHEN r.request_id = 131 THEN r.request_id END) wr131,
         UNISTR (CASE WHEN r.request_id = 143 THEN r.request_id END) wr143
    FROM lab_requests_head_view h
         JOIN lab_requests_requrements r ON r.request_id = h.request_id
   WHERE h.water_type_id = 3 AND h.water_type = 2
ORDER BY h.request_number;

Upvotes: 0

Related Questions