![\"\"](\"http:\/\/van-maanen.com\/wp-content\/uploads\/2019\/04\/Untitled-3-3.png\")
<\/figcaption><\/figure>\n\n\n\n
The target situation is:<\/p>\n\n\n\n We have the code for the table here:<\/p>\n\n\n\n There are two solutions for this:<\/p>\n\n\n\n Solution 1 uses the pivot function:<\/p>\n\n\n\n Solution 2 uses a decode function:<\/p>\n\n\n\n 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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-3039","post","type-post","status-publish","format-standard","hentry","category-allgemein"],"_links":{"self":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/3039","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3039"}],"version-history":[{"count":0,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/3039\/revisions"}],"wp:attachment":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3039"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3039"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3039"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}![\"\"](\"http:\/\/van-maanen.com\/wp-content\/uploads\/2019\/04\/Untitled-3-1-1-1.png\")
<\/figure>\n\n\n\nCREATE TABLE pivot_test (
\n id NUMBER,
\n customer_id NUMBER,
\n product_code VARCHAR2(5),
\n quantity NUMBER
\n);<\/pre>\n\n\n\nINSERT INTO pivot_test VALUES (1, 1, 'A', 10);
\nINSERT INTO pivot_test VALUES (2, 1, 'B', 20);
\nINSERT INTO pivot_test VALUES (3, 1, 'C', 30);
\nINSERT INTO pivot_test VALUES (4, 2, 'A', 40);
\nINSERT INTO pivot_test VALUES (5, 2, 'C', 50);
\nINSERT INTO pivot_test VALUES (6, 3, 'A', 60);
\nINSERT INTO pivot_test VALUES (7, 3, 'B', 70);
\nINSERT INTO pivot_test VALUES (8, 3, 'C', 80);
\nINSERT INTO pivot_test VALUES (9, 3, 'D', 90);
\nINSERT INTO pivot_test VALUES (10, 4, 'A', 100);
\nCOMMIT;<\/pre>\n\n\n\nSELECT \"CUSTOMER_ID\" ,
\n \"A_SUM_QUANTITY\" ,
\n \"B_SUM_QUANTITY\" ,
\n \"C_SUM_QUANTITY\"
\nFROM (SELECT customer_id, product_code, quantity
\n FROM pivot_test)
\nPIVOT (SUM(quantity) AS sum_quantity FOR (product_code) IN ('A' AS a, 'B' AS b, 'C' AS c))
\nORDER BY customer_id;<\/pre>\n\n\n\nSELECT customer_id,
\n SUM(DECODE(product_code, 'A', quantity, 0)) AS a_sum_quantity,
\n SUM(DECODE(product_code, 'B', quantity, 0)) AS b_sum_quantity,
\n SUM(DECODE(product_code, 'C', quantity, 0)) AS c_sum_quantity
\nFROM pivot_test
\nGROUP BY customer_id
\nORDER BY customer_id;<\/pre>\n","protected":false},"excerpt":{"rendered":"