Transforming rows into columns

SQL_Developer

I have ant an Oracle v11 database, and whilst I do not have the schema definition of the tables, I have illustrated what I am trying to achieve below.

This is what the table looks like

I am trying to transform the data by selecting only the latest rows, the table keeps an history of changes, I am not interested in the changes only the latest value for every present issue

This is what I have so far.

select issueno,
case (when fieldname = 'name' then string_value end) name,
case (when fieldname = 'point' then string_value end) point
from issues
where issueno = 1234

The issue with the query above is that it returns 4 rows, I would like to return only a single row. I cant use a max as the column is of clob data type which I cannot change.

mathguy

The CASE use is correct. In addition to that, you need aggregation. Since you must select values from one column, based on ordering in a different column, you need the LAST aggregate function (read about it if you are not familiar with it). Also, the ordering is conditional: for each output column, you must only consider the dates for the corresponding FIELDNAME. See how that is done using CASE again, in the ORDER BY clause of LAST.

select issueno, 
       min(case fieldname when 'name' then string_value end)
           keep (dense_rank last
                 order by case fieldname when 'name' then transition_date end nulls first) as name,
       min(case fieldname when 'point' then to_number(string_value) end)
           keep (dense_rank last
                 order by case fieldname when 'point' then transition_date end nulls first) as point
from   issues
group  by issueno
;

Note also the use of TO_NUMBER() on STRING_VALUE for the POINT expression. Get the output columns in the right data types.

mathguy

On re-reading your question, I see that I missed an important bit - "the column" is CLOB. You didn't say which column, but I assume it's STRING_VALUE, and if so, then indeed you can't just take MAX over it (or over a CASE expression returning it).

Here is a different solution, which uses analytic functions instead of aggregations, in a way that will work with CLOB. It does more work, but perhaps it will be OK for you.

with has_rn as (
  select issueno, fieldname, string_value,
         row_number() over (partition by issueno, fieldname order by transition_date desc) as rn
  from   issues
  where  fieldname in ('name', 'point')
)
select issueno, t1.string_value as name, to_number(t2.string_value) as point
from   (select issueno, string_value from has_rn where fieldname = 'name'  and rn = 1) t1
       full outer join 
       (select issueno, string_value from has_rn where fieldname = 'point' and rn = 1) t2
       using (issueno)
;

Although, if the strings stored in the CLOB column are reasonably long (under 4000 bytes) when FIELDNAME is in ('name', 'point') (even if it is much longer for other field names), you could instead just truncate the STRING_VALUE and use the aggregate query from my first answer. Like this:

select issueno, 
       min(case fieldname when 'name' then cast(string_value as varchar2(4000)) end)
           keep (dense_rank last
                 order by case fieldname when 'name' then transition_date end nulls first) as name,
       min(case fieldname when 'point' then to_number(cast(string_value as varchar2(4000))) end)
           keep (dense_rank last
                 order by case fieldname when 'point' then transition_date end nulls first) as point
from   issues
group  by issueno
;