CODEWITHSUNDEEP
sqloracle-databasepivot
Julio
Julio

Reputation: 551

pivot table with two rows into columns

i have the next result for my table:

our_date | number_people
------------------------
23/09/19 |  26
24/09/19 |  26

ALWAYS will be just two rows

and i want pivot this result and get this:

our_date_1   | number_people_1   | our_date_2   | number_people_2
-----------------------------------------------------------------
23/09/19     |  26               | 24/09/19     |   26

to get the differences between number_people_1 and number_people_2

i try with:

select *
from table_1
pivot(
    count(number_people)
    for our_date in (:P_TODAY, :P_YESTERDAY)
)

and this is my actual error:

ORA-56900: la variable de enlace no está soportada en la operación PIVOT|UNPIVOT
56900. 0000 -  "bind variable is not supported inside pivot|unpivot operation"
*Cause:    Attempted to use bind variables inside pivot|unpivot operation.
*Action:   This is not supported.

what's is wrong? how can i use dynamic values inside for clause ?

Best regards

Upvotes: 0

Views: 1144

Answers (1)

Littlefoot
Littlefoot

Reputation: 143163

Error says that this:

for fecha in (our_date)

can't have our_date (column name) as list of values; it (the list) has to contain constants, e.g. 

for our_date in (date '2019-09-23', date '2019-09-24')

Once you fix that, query might look like this:

SQL> with table_1 (our_date, number_people) as
  2    (select date '2019-09-23', 26 from dual union all
  3     select date '2019-09-24', 26 from dual
  4    )
  5  select *
  6  from table_1
  7  pivot (max(number_people)
  8         for our_date in (date '2019-09-23', date '2019-09-24')
  9        );

TO_DATE(' 2019-09-23 00:00:00' TO_DATE(' 2019-09-24 00:00:00'
------------------------------ ------------------------------
                            26                             26

SQL>

But, that's not exactly what you wanted.

What if there are 3, 4 or more rows in that table? Is it possible, or will there always be only 2 rows?

If it is always only 2 rows, self-join can do the job. For example:

SQL> with table_1 (our_date, number_people) as
  2    (select date '2019-09-23', 26 from dual union all
  3     select date '2019-09-24', 22 from dual
  4    ),
  5  temp as
  6    (select our_date, number_people,
  7       row_number() over (order by our_date) rn
  8     from table_1
  9    )
 10  select
 11    a.our_date our_date_1,
 12    a.number_people number_people_1,
 13    --
 14    b.our_date our_date_2,
 15    b.number_people number_people_2
 16  from temp a cross join temp b
 17  where a.rn = 1
 18    and b.rn = 2;

OUR_DATE_1 NUMBER_PEOPLE_1 OUR_DATE_2 NUMBER_PEOPLE_2
---------- --------------- ---------- ---------------
23.09.2019              26 24.09.2019              22

SQL>

Upvotes: 1

Related Questions