Pivot in SQL: count not working as expected

Question

I have in my Oracle Responsys Database a table that contains records with amongst other two variables:

status
location_id

I want to count the number of records grouped by status and location_id, and display it as a pivot table.

This seems to be the exact example that appears here

But when I use the following request :

select * from 
    (select status,location_id from $a$ ) 
        pivot (count(status) 
        for location_id in (0,1,2,3,4)
    ) order by status

The values that appear in the pivot table are just the column names :

output :

status    0    1    2    3    4
-1        0    1    2    3    4
1         0    1    2    3    4
2         0    1    2    3    4
3         0    1    2    3    4
4         0    1    2    3    4
5         0    1    2    3    4

I also gave a try to the following :

select * from 
     (select status,location_id , count(*) as nbreports 
       from $a$ group by status,location_id ) 
              pivot (sum(nbreports) 
              for location in (0,1,2,3,4)
    ) order by status

but it gives me the same result.

 select status,location_id , count(*) as nbreports 
 from $a$ 
 group by status,location_id

will of course give me the values I want, but displaying them as a column and not as a pivot table

How can I get the pivot table to have in each cell the number of records with the status and location in row and column?

Example data:

CUSTOMER,STATUS,LOCATION_ID
1,-1,1
2,1,1
3,2,1
4,3,0
5,4,2
6,5,3
7,3,4

The table data types :

CUSTOMER    Text Field (to 25 chars)
STATUS  Text Field (to 25 chars)
LOCATION_ID Number Field

Can you provide your create table and inserts for your sample data that's leading to these results? — Chris Saxon, Jul 13 '17 at 14:53
I cannot create a table or insert data using SQL in my database : PL/SQL is just used in Oracle Responsys to make SELECT requests to display processed data, but not to create data. — WNG, Jul 13 '17 at 17:27
@WilliamRobertson I find your comment a bit cryptic. Care to elaborate? — WNG, Jul 13 '17 at 21:44
It looks like a question about Oracle SQL. PL/SQL is an in-database programming language for creating procedures etc. — William Robertson, Jul 13 '17 at 21:50
@WNG you don't need to be able to create or insert the data; we need you to provide us with the table definition and data so we can recreate the your example and see why you're getting "values that are column names" — Chris Saxon, Jul 14 '17 at 08:47

score 2 · Accepted Answer · answered Jul 14 '17 at 04:47

Please check if my understanding for your requirement is correct, you can do vice versa for the location column

    create table test(
    status varchar2(2),
    location number
    );

    insert into test values('A',1);
    insert into test values('A',2);
    insert into test values('A',1);
    insert into test values('B',1);
    insert into test values('B',2);

    select * from test;

    select status,location,count(*)
    from test 
    group by status,location;

    select * from (
    select status,location
    from test 
    ) pivot(count(*) for (status) in ('A' as STATUS_A,'B' as STATUS_B))

Thanks, it actually worked what was missing from my request was assigning names to the columns. adding the "as" for each element in the "for ... in" list did the trick! — WNG, Jul 14 '17 at 14:33

Pivot in SQL: count not working as expected

1 Answers1