Is there a way to simply merge via a single pivot or analytical query the results from two rows into one? We have a legacy table that is essentially storing key value pairs in columnar format. As an test case, here is an example:
create table kvtest ( App_ID Number, User_Id Number, strKey varchar2(10), strValue varchar2(20));
insert into kvtest select 1, 100, 'Color', 'Blue' from dual;
insert into kvtest select 1, 200, 'Location', 'USA' from dual;
commit;
The output of select * from kvtest:
APP_ID USER_ID STRKEY STRVALUE
---------- ---------- ---------- --------------------
1 200 Location USA
1 100 Color Blue I'd like to combine key:values by an app_id regardless of the user_id which in this example will always be limited to 2 rows (meaning there will not be 3 user_ids per app_id, only 2).
My desired output would look like this:
APP_ID LOCATION COLOR
---------- -------------------------------- ------------
1 USA Blue Thanks!
Sure. Pivot will help you here.
- In a subquery select the columns that you want in your output or will pivot by (the database will implicitly group by columns NOT in the pivot)
- Then list out the keys you want to become columns in the pivot clause:
with rws as (
select app_id, strkey, strvalue from kvtest
)
select * from rws
pivot (
min ( strvalue ) for strkey in (
'Color' color, 'Location' location
)
);
APP_ID COLOR LOCATION
1 Blue USA If you want to know more about pivoting, read:
https://blogs.oracle.com/sql/how-to-convert-rows-to-columns-and-back-again-with-sql-aka-pivot-and-unpivot