Changing granularity

Let us suppose, we have a table that has a granularity in two dimensions. Think of a customer and a product dimension. So each record has a combination of customer identification and product identification that is unique. Let us also assume that we would decrease that level of granularity into one dimension. Think of customer that should act as the identifying dimension. Each product identification should have its own column.

Let us provide an example. The start situation is as follows:

The target situation is:

We have the code for the table here:

CREATE TABLE pivot_test (

  id            NUMBER,

  customer_id   NUMBER,

  product_code  VARCHAR2(5),

  quantity      NUMBER

);

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

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

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

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

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

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

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

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

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

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

COMMIT;

There are two solutions for this:

Solution 1 uses the pivot function:

SELECT "CUSTOMER_ID" , 

    "A_SUM_QUANTITY" , 

    "B_SUM_QUANTITY" , 

    "C_SUM_QUANTITY"

FROM   (SELECT customer_id, product_code, quantity

        FROM   pivot_test)

PIVOT  (SUM(quantity) AS sum_quantity FOR (product_code) IN ('A' AS a, 'B' AS b, 'C' AS c))

ORDER BY customer_id;

Solution 2 uses a decode function:

SELECT customer_id,

       SUM(DECODE(product_code, 'A', quantity, 0)) AS a_sum_quantity,

       SUM(DECODE(product_code, 'B', quantity, 0)) AS b_sum_quantity,

       SUM(DECODE(product_code, 'C', quantity, 0)) AS c_sum_quantity

FROM   pivot_test

GROUP BY customer_id

ORDER BY customer_id;