CODEWITHSUNDEEP
oracle-databaseoracle11g
JayG30
JayG30

Reputation: 79

join tables and turn row into column, Oracle 11g

I'm working with an Oracle 11g database. I have to join multiple tables together and one of those tables contains result values for multiple tests where each result is a row. When I combine tables I want to condense these multiple test results into a single row.

Below is a reduced example of what I have. I'm querying data from three tables called requisitions, tests, and results for specific tests.

SELECT
  requisitions.acc_id
  ,tests.test_name
  ,results.result_numeric
FROM requisitions

inner join req_panels ON requisitions.acc_id = req_panels.acc_id
inner join results ON req_panels.rp_id = results.rp_id
inner join tests ON results.test_id = tests.test_id

WHERE results.test_id IN (1,2,7,8)
ORDER BY requisitions.acc_id

This results in this;

Acc_ID | Test_Name | Result_Numeric
------------------------------------
000001 | Test 1    | 24
000001 | Test 2    | 1.5
000002 | Test 1    | 15
000002 | Test 2    | 2.1

But what I'd like to get is;

Acc_ID | Test 1 | Test 2
--------------------------
000001 |   24   |  1.5
000002 |   15   |  2.1

Hopefully this is clear. Do I have to use pivot or something else? I'm still relatively new to pivots and haven't been able to comprehend how to use them yet.

Thanks.

EDIT: This is what I came up with using pivot. Is my first attempt at using pivot so if I'm doing something wrong or something that could be done more efficiently just let me know.

SELECT * FROM (
  SELECT requisitions.acc_id
  ,tests.TEST_ID
  ,results.RESULT_NUMERIC
FROM requisitions

inner join req_panels ON requisitions.acc_id = req_panels.acc_id
inner join results ON req_panels.rp_id = results.rp_id
inner join tests ON results.test_id = tests.test_id

WHERE results.TEST_ID IN (1,2,3)
)
pivot(
      MAX(RESULT_NUMERIC)
      for TEST_ID IN ('1' AS Test 1,'2' AS Test 2,'3' AS Test 3)
)
ORDER BY ACC_ID

Upvotes: 0

Views: 118

Answers (1)

Taryn
Taryn

Reputation: 247860

You should be able to get the final result by using an aggregate function with a CASE expression:

SELECT r.acc_id,
  max(case when t.test_name = 'Test 1' then rs.result_numeric end) Test1,
  max(case when t.test_name = 'Test 2' then rs.result_numeric end) Test2
FROM requisitions r
inner join req_panels rp
  ON r.acc_id = rp.acc_id
inner join results rs
  ON rp.rp_id = rs.rp_id
inner join tests t
  ON rs.test_id = t.test_id
WHERE rs.test_id IN (1,2,7,8)
GROUP BY r.acc_id
ORDER BY r.acc_id;

Or you could use the PIVOT syntax:

select acc_id, Test1, Test2
from
(
  select r.acc_id, t.test_name, rs.result_numeric
  FROM requisitions r
  inner join req_panels rp
    ON r.acc_id = rp.acc_id
  inner join results rs
    ON rp.rp_id = rs.rp_id
  inner join tests t
    ON rs.test_id = t.test_id
  WHERE rs.test_id IN (1,2,7,8)
) d
pivot
(
  max(result_numeric)
  for test_name in ('Test 1' as Test1, 'Test 2' as Test2)
) piv;

Upvotes: 2

Related Questions