oracle combination two column distinct value into multiple column

Question

I am working with Solr where per product only 1 row is allowed. Requirement is my site supports multiple market and every market multiple currency which leads to product price. Pictured below

  ProductId MarketId CurrencyId Price
   P1---M1---C1---100
   P1---M1---C2---200
   P1---M2---C1---300
   P1---M2---C2---400
   P2---M3---C3---500
   P2---M1---C1---600

Output needed is

PrId--M1C1--M1C2--M2C1--M2C2--M3C3
P1----100---200---300---400---NULL
P2----600---NULL--NULL--NULL--500

I have seen suggestion to use pivot here but that couldn't explain answer.
Edited: Market and currency are dynamic value, it should be form distinct columns in the end result

P2 and P3 are not as expected as per standard PIVOT in the output shown. — Lalit Kumar B, Feb 27 '15 at 09:54

Ponder Stibbons · Answer 1 · 2015-02-27T16:41:06.880

3

For defined group of possible pairs you can build query like below.

select productid pid, 
    sum (case when marketid='M1' and currencyid='C1' then price else null end) m1c1,
    sum (case when marketid='M1' and currencyid='C2' then price else null end) m1c2,
    sum (case when marketid='M2' and currencyid='C1' then price else null end) m2c1,
    sum (case when marketid='M2' and currencyid='C2' then price else null end) m2c2,
    sum (case when marketid='M3' and currencyid='C3' then price else null end) m3c3
  from products
  group by productid

Results:

PID              M1C1       M1C2       M2C1       M2C2       M3C3
---------- ---------- ---------- ---------- ---------- ----------
P1                100        200        300        400 
P2                                                            500
P3                600

To make this dynamically you can create PLSQL procedure, I think.

Edit: "Dynamic" solution with procedure building view v_products:

begin p_products; end;
select * from v_products;

Output:
PID             M1_C1      M1_C2      M2_C1      M2_C2      M3_C3
---------- ---------- ---------- ---------- ---------- ----------
P1                100        200        300        400 
P2                                                            500
P3                600

Procedure p_products code:

create or replace procedure p_products is
  v_sql varchar2(4000) := 
    'create or replace view v_products as select productid pid, ';
begin
  for o in (
    select distinct marketid mid, currencyid cid from products
      order by marketid, currencyid)
  loop
    v_sql := v_sql||' sum (case when marketid='''||o.mid
      ||''' and currencyid='''||o.cid||''' then price else null end) '
      ||o.mid||'_'||o.cid||', ';
  end loop;
  v_sql := rtrim(v_sql, ', ');
  v_sql := v_sql||'  from products group by productid';

  execute immediate v_sql;
end;

edited Feb 27 '15 at 16:41

answered Feb 27 '15 at 11:06

Ponder Stibbons

14,723
2
21
24

1

You don't need `ELSE NULL` ... that's the default. Otherwise great work. – David Faber Feb 27 '15 at 12:22
Thanks for the response this looks acceptable as you said for fixed values. but market and currency both are dynamic. – sanjay patel Feb 27 '15 at 12:43
Dynamic values require a table where you save those values then you query those. How else would you deal with dynamic data if you do not know the value in advance? – Art Feb 27 '15 at 16:38
@Art - I just added second part of answer with my idea for dynamic solution. – Ponder Stibbons Feb 27 '15 at 17:00
@Ponder Stibbons: Yes, this works, I wish I could upvote you twice. Table or view, both works and should be used in this case. – Art Feb 27 '15 at 17:24
How will the dynamic pivot work with Solr, though? Yes, you can have dynamic fields in your Solr schema that can be populated at index time, but how will that work at query time? Not to take anything away from @PonderStibbons great work. – David Faber Feb 27 '15 at 19:45
@David Faber - I know too little about Solr to determine whether it solves the OP's problem, but may be useful. And thank you for pointing that `else null`. At first I wrote `else 0` but then changed my mind and thoughtlessly replaced `0` with `null`. – Ponder Stibbons Feb 27 '15 at 20:00
Agreed that it's useful - that's why I upvoted. My comment was more for the OP than for you. – David Faber Feb 27 '15 at 20:02
@David: Agreed. dynamic pivot can't be used in data import handler and schema files. I was in dilemma if dynamic pivot is PL SQL thing or a inbuilt oracle keyword which can be used directly. After searching little more found it is only PL SQL. Finally we decided create a Procedure which created temporary table with required data, and use them in build index. – sanjay patel Feb 28 '15 at 10:47

score 1 · Answer 2 · edited May 23 '17 at 12:29

It's true that you can't have a unique identifier in Solr made up of more than one field. However, you can generate a concatenated key and use that as your unique identifier. In Oracle you might do this as follows (I'm assuming productId + marketId + currencyId are unique):

SELECT productId || '-' || marketId || '-' || currencyId AS unique_id
     , productId, marketId, currencyId, price
  FROM mytable;

Doing it this way would have the advantage of making range queries easy over markets (of course they would probably not be useful over currencies), and it is also dynamic where a manual pivot or even using the PIVOT function would not be.

oracle combination two column distinct value into multiple column

2 Answers2