What is the correct way of counting every column by itself in Oracle

Question

I have a table which has several columns. Except customer column, I want to count them all by themselves. I need to see how many transactions done on "value1" by "customer:100" in segment "1". Which is in this scenario at the bottom, answer is 2.

customer	value1	value2	value3	value4	value5	value6	value7	value8
100	1	2	5	3	2	1	2	1
100	1	3	4	3	2	5	2	1
101	3	2	2	2	2	1	2	1
101	3	2	1	2	1	3	5	2
101	2	2	5	4	2	3	4	3

I need something like this:

customer	columns	1	2	3	4	5
100	value1	2	0	0	0	0
100	value2	0	1	1	0	0
100	value3	0	0	0	1	1
100	value4	0	0	2	0	0
100	value5	0	2	0	0	0
101	value1	0	1	2	0	0
101	value2	0	3	0	0	0
101	value3	1	1	0	0	1
101	value4	0	2	0	1	0
101	value5	1	2	0	0	0

But I wonder what is the correct way of doing this?

Thanks in advance.

Answer 1

UNPIVOT the columns and then PIVOT on the values:

SELECT *
FROM   table_name
UNPIVOT (
  value
  FOR columns IN (value1, value2, value3, value4, value5, value6, value7, value8)
)
PIVOT (
  COUNT(*)
  FOR value IN (1, 2, 3, 4, 5)
)

Which, for the sample data:

CREATE TABLE table_name (customer, value1, value2, value3, value4, value5, value6, value7, value8) AS
SELECT 100, 1, 2, 5, 3, 2, 1, 2, 1 FROM DUAL UNION ALL
SELECT 100, 1, 3, 4, 3, 2, 5, 2, 1 FROM DUAL UNION ALL
SELECT 101, 3, 2, 2, 2, 2, 1, 2, 1 FROM DUAL UNION ALL
SELECT 101, 3, 2, 1, 2, 1, 3, 5, 2 FROM DUAL UNION ALL
SELECT 101, 2, 2, 5, 4, 2, 3, 4, 3 FROM DUAL;

Outputs:

CUSTOMER	COLUMNS	1	2	3	4	5
100	VALUE1	2	0	0	0	0
100	VALUE2	0	1	1	0	0
100	VALUE3	0	0	0	1	1
100	VALUE4	0	0	2	0	0
100	VALUE5	0	2	0	0	0
100	VALUE6	1	0	0	0	1
100	VALUE7	0	2	0	0	0
100	VALUE8	2	0	0	0	0
101	VALUE1	0	1	2	0	0
101	VALUE2	0	3	0	0	0
101	VALUE3	1	1	0	0	1
101	VALUE4	0	2	0	1	0
101	VALUE5	1	2	0	0	0
101	VALUE6	1	0	2	0	0
101	VALUE7	0	1	0	1	1
101	VALUE8	1	1	1	0	0

fiddle