Pivot Seesion01

View All Public Scripts Login to Run Script

Script Name Pivot Seesion01
Visibility Unlisted - anyone with the share link can access
Description Pivot Session For Ask Tom
Contributor Semi Fisnished Goods
Created Thursday April 20, 2017

Statement 1
```
select * from pivot_test
```
```
ORA-00942: table or view does not exist
```
More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 2

CREATE TABLE pivot_test ( 
  id            NUMBER, 
  customer_id   NUMBER, 
  product_code  VARCHAR2(5), 
  quantity      NUMBER 
)

Table created.

Statement 3

INSERT INTO pivot_test VALUES (1, 1, 'A', 10)

1 row(s) inserted.

Statement 4

INSERT INTO pivot_test VALUES (2, 1, 'B', 20)

1 row(s) inserted.

Statement 5

INSERT INTO pivot_test VALUES (3, 1, 'C', 30)

1 row(s) inserted.

Statement 6

INSERT INTO pivot_test VALUES (4, 2, 'A', 40)

1 row(s) inserted.

Statement 7

INSERT INTO pivot_test VALUES (5, 2, 'C', 50)

1 row(s) inserted.

Statement 8

INSERT INTO pivot_test VALUES (6, 3, 'A', 60)

1 row(s) inserted.

Statement 9

INSERT INTO pivot_test VALUES (7, 3, 'B', 70)

1 row(s) inserted.

Statement 10

INSERT INTO pivot_test VALUES (8, 3, 'C', 80)

1 row(s) inserted.

Statement 11

INSERT INTO pivot_test VALUES (9, 3, 'D', 90)

1 row(s) inserted.

Statement 12

INSERT INTO pivot_test VALUES (10, 4, 'A', 100)

1 row(s) inserted.

Statement 13
```
COMMIT
```
```
Statement processed.
```

Statement 14

select * from pivot_test


ID CUSTOMER_ID PRODUCT_CODE QUANTITY
1 1 A 10
2 1 B 20
3 1 C 30
4 2 A 40
5 2 C 50
6 3 A 60
7 3 B 70
8 3 C 80
9 3 D 90
10 4 A 100

10 rows selected.

ID	CUSTOMER_ID	PRODUCT_CODE	QUANTITY
1	1	A	10
2	1	B	20
3	1	C	30
4	2	A	40
5	2	C	50
6	3	A	60
7	3	B	70
8	3	C	80
9	3	D	90
10	4	A	100

Statement 15

select * from (select product_code,quantity from pivot_test) pivot(count(quantity) for product_code in ('A','B','C','D'))


'A' 'B' 'C' 'D'
4 2 3 1

'A'	'B'	'C'	'D'
4	2	3	1

Statement 16

select * from (select product_code,quantity from pivot_test) pivot(count(*) for product_code in ('A','B','C','D'))


QUANTITY 'A' 'B' 'C' 'D'
30 0 0 1 0
100 1 0 0 0
20 0 1 0 0
70 0 1 0 0
90 0 0 0 1
40 1 0 0 0
50 0 0 1 0
80 0 0 1 0
10 1 0 0 0
60 1 0 0 0

10 rows selected.

QUANTITY	'A'	'B'	'C'	'D'
30	0	0	1	0
100	1	0	0	0
20	0	1	0	0
70	0	1	0	0
90	0	0	0	1
40	1	0	0	0
50	0	0	1	0
80	0	0	1	0
10	1	0	0	0
60	1	0	0	0

Statement 17

select * from pivot_test--Total Rows 10


ID CUSTOMER_ID PRODUCT_CODE QUANTITY
1 1 A 10
2 1 B 20
3 1 C 30
4 2 A 40
5 2 C 50
6 3 A 60
7 3 B 70
8 3 C 80
9 3 D 90
10 4 A 100

10 rows selected.

Statement 18
```
commit
```
```
Statement processed.
```

ID	CUSTOMER_ID	PRODUCT_CODE	QUANTITY
1	1	A	10
2	1	B	20
3	1	C	30
4	2	A	40
5	2	C	50
6	3	A	60
7	3	B	70
8	3	C	80
9	3	D	90
10	4	A	100

Statement 19

insert into pivot_test values(11,11,'A',10)

1 row(s) inserted.

Statement 20

select * from (select product_code,quantity from pivot_test) pivot(count(*) for product_code in ('A','B','C','D'))


QUANTITY 'A' 'B' 'C' 'D'
30 0 0 1 0
100 1 0 0 0
20 0 1 0 0
70 0 1 0 0
90 0 0 0 1
40 1 0 0 0
50 0 0 1 0
80 0 0 1 0
10 2 0 0 0
60 1 0 0 0

10 rows selected.

QUANTITY	'A'	'B'	'C'	'D'
30	0	0	1	0
100	1	0	0	0
20	0	1	0	0
70	0	1	0	0
90	0	0	0	1
40	1	0	0	0
50	0	0	1	0
80	0	0	1	0
10	2	0	0	0
60	1	0	0	0

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database

QUANTITY	'A'	'B'	'C'	'D'
30	0	0	1	0
100	1	0	0	0
20	0	1	0	0
70	0	1	0	0
90	0	0	0	1
40	1	0	0	0
50	0	0	1	0
80	0	0	1	0
10	1	0	0	0
60	1	0	0	0

QUANTITY	'A'	'B'	'C'	'D'
30	0	0	1	0
100	1	0	0	0
20	0	1	0	0
70	0	1	0	0
90	0	0	0	1
40	1	0	0	0
50	0	0	1	0
80	0	0	1	0
10	2	0	0	0
60	1	0	0	0

QUANTITY	'A'	'B'	'C'	'D'
30	0	0	1	0
100	1	0	0	0
20	0	1	0	0
70	0	1	0	0
90	0	0	0	1
40	1	0	0	0
50	0	0	1	0
80	0	0	1	0
10	1	0	0	0
60	1	0	0	0

QUANTITY	'A'	'B'	'C'	'D'
30	0	0	1	0
100	1	0	0	0
20	0	1	0	0
70	0	1	0	0
90	0	0	0	1
40	1	0	0	0
50	0	0	1	0
80	0	0	1	0
10	2	0	0	0
60	1	0	0	0

QUANTITY	'A'	'B'	'C'	'D'
30	0	0	1	0
100	1	0	0	0
20	0	1	0	0
70	0	1	0	0
90	0	0	0	1
40	1	0	0	0
50	0	0	1	0
80	0	0	1	0
10	1	0	0	0
60	1	0	0	0

QUANTITY	'A'	'B'	'C'	'D'
30	0	0	1	0
100	1	0	0	0
20	0	1	0	0
70	0	1	0	0
90	0	0	0	1
40	1	0	0	0
50	0	0	1	0
80	0	0	1	0
10	2	0	0	0
60	1	0	0	0