Selecting independent rows and displaying them into a single row (ORACLE SQL)

Question

I have a table called requesttool.request_detail which is used to store attributes for entities identified by the value in column REQUEST_ID. The table requesttool.request_detail has a column called ATTRIBUTE_ID which indicates what is stored in the respective row of another column called ATTRIBUTE_VALUE. For instance, if ATTRIBUTE_ID='259' for a given row then name will be stored in that respective row of ATTRIBUTE_VALUE.

Here is what requesttool.request_detail looks like in practice:

What I want to do is to extract the value stored in ATTRIBUTE_VALUE for 3 different ATTRIBUTE_ID's and for a given REQUEST_ID, say 4500161635, and display them in a single row, like this:

I have tried the following code:

select
  request_id,
  case when attribute_id = '289' then attribute_value end as name,
  case when attribute_id = '259' then attribute_value end as country,
  case when attribute_id = '351' then attribute_value end as priority
from (
  select a.request_id, a.attribute_id, a.attribute_value
  from requesttool.request_detail a
  where a.request_id='4500161635');

but from this I obtain a table with null values, not a single line:

You Can Group by Request_id and select max(case ..) for all 3 columns. — Kobi
– Kobi, Commented Oct 6, 2016 at 7:35

Thorsten Kettner · Accepted Answer · 2016-10-06 07:33:00Z

1

You are on the right track. Only you'd have to aggregate your rows so as to get one result row per request_id:

select
  request_id,
  max(case when attribute_id = '289' then attribute_value end) as name,
  max(case when attribute_id = '259' then attribute_value end) as country,
  max(case when attribute_id = '351' then attribute_value end) as priority
from requesttool.request_detail
where request_id = '4500161635'
group by request_id;

Given an index on request_id + attribute_id, you might be able to speed this up by adding a condition to your where clause:

and attribute_id in ('289', '259', '351')

BTW: Are request_id and attribute_id really strings or why are you using quotes on the numbers?

answered Oct 6, 2016 at 7:33

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jaydip Jadhav · Accepted Answer · 2016-10-06 07:33:32Z

1

Try this

select
  request_id,
  MIN(case when attribute_id = '289' then attribute_value end) as name,
  MIN(case when attribute_id = '259' then attribute_value end) as country,
  MIN(case when attribute_id = '351' then attribute_value end) as priority
from (
     select a.request_id, a.attribute_id, a.attribute_value
     from requesttool.request_detail a
     where a.request_id='4500161635')
 GROUP BY request_id

answered Oct 6, 2016 at 7:33

Jaydip Jadhav

12.3k6 gold badges26 silver badges41 bronze badges

Collectives™ on Stack Overflow

Selecting independent rows and displaying them into a single row (ORACLE SQL)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related