CODEWITHSUNDEEP
sqloracle-database
Filipe Aleixo
Filipe Aleixo

Reputation: 4242

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

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:

enter image description here

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:

enter image description here

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:

enter image description here

Upvotes: 1

Views: 101

Answers (2)

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

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

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 95072

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?

Upvotes: 1

Related Questions