Home » SQL & PL/SQL » SQL & PL/SQL » transforming rows to columns (oracle 10 G and 11 G)
transforming rows to columns [message #658638] Thu, 22 December 2016 06:00 Go to next message
akash123
Messages: 42
Registered: May 2008
Location: india
Member
I am trying to display data rows as columns. I tried using pivot and it worked to some extent but problem is IN clause i need to hard code the values here. How can i pass the data to that IN clause using a sub query.
CREATE  TABLE test_data (id NUMBER,customer_id   NUMBER,product_code  VARCHAR2(5),quantity NUMBER)
INSERT INTO test_data VALUES (1, 1, 'A', 10);
INSERT INTO test_data VALUES (2, 1, 'B', 20);
INSERT INTO test_data VALUES (3, 1, 'C', 30);
INSERT INTO test_data VALUES (4, 2, 'A', 40);
INSERT INTO test_data VALUES (5, 2, 'C', 50);
i tried this
SELECT * FROM(SELECT CUSTOMER_ID,PRODUCT_CODE,QUANTITY FROM test_data )
PIVOT  (SUM(QUANTITY) AS SUM_QUANTITY FOR (PRODUCT_CODE) IN ('A' , 'B' , 'C' ));

Re: transforming rows to columns [message #658640 is a reply to message #658638] Thu, 22 December 2016 06:32 Go to previous messageGo to next message
akash123
Messages: 42
Registered: May 2008
Location: india
Member
any one in the group, please guide me in the issue
Re: transforming rows to columns [message #658643 is a reply to message #658638] Thu, 22 December 2016 07:06 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2699
Registered: January 2010
Location: Connecticut, USA
Senior Member
akash123 wrote on Thu, 22 December 2016 07:00
How can i pass the data to that IN clause using a sub query.
Not possible. You could write ODCI interface based pivot (quite complex task) and still, it will not cover 100% of cases. Or you can decide on max number of products (e.g. 10):

WITH T AS (
           SELECT  CUSTOMER_ID,
                   QUANTITY,
                   DENSE_RANK() OVER(ORDER BY PRODUCT_CODE) PRODUCT_CODE_RNK
             FROM  TEST_DATA
          )
SELECT  CUSTOMER_ID,
        "1_SUM_QUANTITY"  AS PROD1_QTY,
        "2_SUM_QUANTITY"  AS PROD2_QTY,
        "3_SUM_QUANTITY"  AS PROD3_QTY,
        "4_SUM_QUANTITY"  AS PROD4_QTY,
        "5_SUM_QUANTITY"  AS PROD5_QTY,
        "6_SUM_QUANTITY"  AS PROD6_QTY,
        "7_SUM_QUANTITY"  AS PROD7_QTY,
        "8_SUM_QUANTITY"  AS PROD8_QTY,
        "9_SUM_QUANTITY"  AS PROD9_QTY,
        "10_SUM_QUANTITY" AS PROD10_QTY
  FROM  T
  PIVOT (
         SUM(QUANTITY) AS SUM_QUANTITY
         FOR (PRODUCT_CODE_RNK) IN (1,2,3,4,5,6,7,8,9,10)
        )
/

CUSTOMER_ID  PROD1_QTY  PROD2_QTY  PROD3_QTY  PROD4_QTY  PROD5_QTY  PROD6_QTY  PROD7_QTY  PROD8_QTY  PROD9_QTY PROD10_QTY
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
          1         10         20         30
          2         40                    50

SQL>

Now if I add more (up to 10 products):

SQL> INSERT INTO test_data VALUES (6, 1, 'D', 60);

1 row created.

SQL> INSERT INTO test_data VALUES (7, 2, 'E', 70);

1 row created.

SQL> INSERT INTO test_data VALUES (8, 1, 'F', 80);

1 row created.

SQL> WITH T AS (
  2             SELECT  CUSTOMER_ID,
  3                     QUANTITY,
  4                     DENSE_RANK() OVER(ORDER BY PRODUCT_CODE) PRODUCT_CODE_RNK
  5               FROM  TEST_DATA
  6            )
  7  SELECT  CUSTOMER_ID,
  8          "1_SUM_QUANTITY"  AS PROD1_QTY,
  9          "2_SUM_QUANTITY"  AS PROD2_QTY,
 10          "3_SUM_QUANTITY"  AS PROD3_QTY,
 11          "4_SUM_QUANTITY"  AS PROD4_QTY,
 12          "5_SUM_QUANTITY"  AS PROD5_QTY,
 13          "6_SUM_QUANTITY"  AS PROD6_QTY,
 14          "7_SUM_QUANTITY"  AS PROD7_QTY,
 15          "8_SUM_QUANTITY"  AS PROD8_QTY,
 16          "9_SUM_QUANTITY"  AS PROD9_QTY,
 17          "10_SUM_QUANTITY" AS PROD10_QTY
 18    FROM  T
 19    PIVOT (
 20           SUM(QUANTITY) AS SUM_QUANTITY
 21           FOR (PRODUCT_CODE_RNK) IN (1,2,3,4,5,6,7,8,9,10)
 22          )
 23  /

CUSTOMER_ID  PROD1_QTY  PROD2_QTY  PROD3_QTY  PROD4_QTY  PROD5_QTY  PROD6_QTY  PROD7_QTY  PROD8_QTY  PROD9_QTY PROD10_QTY
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
          1         10         20         30         60                    80
          2         40                    50                    70

SQL>

SY.
Re: transforming rows to columns [message #658646 is a reply to message #658643] Thu, 22 December 2016 07:31 Go to previous messageGo to next message
akash123
Messages: 42
Registered: May 2008
Location: india
Member
Thank you solomon for the advise.

problem with the above query is even though there is no data for prod7,prod8,prod9 and prod10 but still we have to display it.
Re: transforming rows to columns [message #658647 is a reply to message #658646] Thu, 22 December 2016 07:37 Go to previous messageGo to next message
Michel Cadot
Messages: 65319
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

How is this a problem?
In your example, there is no data for product B and customer 2, is this a problem?

Re: transforming rows to columns [message #658648 is a reply to message #658646] Thu, 22 December 2016 07:49 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2699
Registered: January 2010
Location: Connecticut, USA
Senior Member
akash123 wrote on Thu, 22 December 2016 08:31

problem with the above query is even though there is no data for prod7,prod8,prod9 and prod10 but still we have to display it.
What you are trying to do is called reporting. And reporting tools have built-in capabilities for that. You can write SQL*Plus script to do the same, but in pure SQL you'll have to write ODCI interface which is, as I already mentioned, quite complex task. But to me it is just picking right tool for the job. What business problem you are trying to solve? Do you need to provide sales report to your business? Then use reporting tool. Most of them allow pivot/unpivot, drill down and many other features allowing different business people to look at data from their angle of view using same report.

SY.
Re: transforming rows to columns [message #658649 is a reply to message #658648] Thu, 22 December 2016 08:09 Go to previous messageGo to next message
akash123
Messages: 42
Registered: May 2008
Location: india
Member

yes its type of a sales report and data get stored in different categories and locations for time being i can fix with the distinct categories available but In future if any new category comes then i need to modify the query instead of just picking from the category master table.
Re: transforming rows to columns [message #658650 is a reply to message #658649] Thu, 22 December 2016 08:58 Go to previous messageGo to next message
Michel Cadot
Messages: 65319
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Explain.

Re: transforming rows to columns [message #658659 is a reply to message #658649] Thu, 22 December 2016 11:14 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2699
Registered: January 2010
Location: Connecticut, USA
Senior Member
akash123 wrote on Thu, 22 December 2016 09:09

yes its type of a sales report
So use reporting tool. Most reporting tools, besides features I already mentioned, have scheduling and publish/subscribe capabilities allowing running report once with many subscribers using results.

SY.
Re: transforming rows to columns [message #658670 is a reply to message #658638] Thu, 22 December 2016 19:38 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8794
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_12.1.0.2.0> SELECT * FROM test_data
  2  /

        ID CUSTOMER_ID PRODU   QUANTITY
---------- ----------- ----- ----------
         1           1 A             10
         2           1 B             20
         3           1 C             30
         4           2 A             40
         5           2 C             50

5 rows selected.

SCOTT@orcl_12.1.0.2.0> VARIABLE g_results REFCURSOR
SCOTT@orcl_12.1.0.2.0> DECLARE
  2    v_sql  VARCHAR2(32767);
  3  BEGIN
  4    v_sql :=
  5  	 'SELECT * FROM (SELECT customer_id,product_code,quantity FROM test_data)
  6  	  PIVOT(SUM(quantity) AS sum_quantity FOR product_code IN (';
  7    FOR i IN (SELECT DISTINCT product_code FROM test_data) LOOP
  8  	 v_sql := v_sql || '''' || i.product_code || ''',';
  9    END LOOP;
 10    v_sql := RTRIM (v_sql, ',') || '))';
 11    OPEN :g_results FOR v_sql;
 12  END;
 13  /

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> PRINT :g_results

CUSTOMER_ID 'A'_SUM_QUANTITY 'B'_SUM_QUANTITY 'C'_SUM_QUANTITY
----------- ---------------- ---------------- ----------------
          1               10               20               30
          2               40                                50

2 rows selected.

Re: transforming rows to columns [message #658675 is a reply to message #658670] Thu, 22 December 2016 23:56 Go to previous message
akash123
Messages: 42
Registered: May 2008
Location: india
Member
thank you Barbara Boehmer
Previous Topic: Convert a sys refcursor into table
Next Topic: Quantity allocation to discount slabs
Goto Forum:
  


Current Time: Tue Jan 23 11:14:30 CST 2018

Total time taken to generate the page: 0.01734 seconds