1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Transpose of data in a table

Discussion in 'SQL PL/SQL' started by budha, Feb 16, 2010.

  1. budha

    budha Guest

    hi friends,
    Code (SQL):

    CREATE TABLE goods(ITEM_NO NUMBER,
                              PRODUCT_NAME varchar2(20),
                              COMPANY_NAME varchar2(20));

    INSERT INTO goods VALUES(1,'TV','SONY');
    INSERT INTO goods VALUES(2,'COMPUTER','HP');
     
    --------------------============--------------------
    Generally when we select the data from a table the o/p format will be...

    ITEM_NO PRODUCT_NAME COMPANY_NAME
    1 TV SONY
    2 COMPUTER HP

    But, I want to print the the table in the below format...

    ITEM_NO 1 2
    PRODUCT_NAME TV COMPUTER
    COMPANY_NAME SONY HP

    Please, help me. without using pivot or any predefined function. Since, I want to work in 9i version
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This appears to be a common pivot example, however you haven't posted which version of Oracle you are using as PIVOT is an 11.1.0.x and later feature. There are other ways to pivot table data, using DECODE (which is rather cumbersome) and by writing your own function (such as Tom Kyte's STRAGG:

    Code (SQL):
    SQL> SELECT *
      2  FROM t;

    A
    ----------
    X
    Y
    SQL> CREATE OR REPLACE TYPE string_agg_type AS OBJECT
      2  (
      3     total VARCHAR2(4000),
      4
      5     STATIC FUNCTION
      6          ODCIAggregateInitialize(sctx IN OUT string_agg_type )
      7          RETURN NUMBER,
      8
      9     MEMBER FUNCTION
     10          ODCIAggregateIterate(SELF IN OUT string_agg_type ,
     11                               VALUE IN VARCHAR2 )
     12          RETURN NUMBER,
     13
     14     MEMBER FUNCTION
     15          ODCIAggregateTerminate(SELF IN string_agg_type,
     16                                 returnValue OUT  varchar2,
     17                                 flags IN NUMBER)
     18          RETURN NUMBER,
     19
     20     MEMBER FUNCTION
     21          ODCIAggregateMerge(SELF IN OUT string_agg_type,
     22                             ctx2 IN string_agg_type)
     23          RETURN NUMBER
     24  );
     25  /

    TYPE created.

    SQL> CREATE OR REPLACE TYPE BODY string_agg_type
      2  IS
      3
      4  STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT string_agg_type)
      5    RETURN NUMBER
      6  IS
      7  BEGIN
      8      sctx := string_agg_type( NULL );
      9      RETURN ODCIConst.Success;
     10  END;
     11
     12  MEMBER FUNCTION ODCIAggregateIterate(SELF IN OUT string_agg_type,
     13                                       VALUE IN varchar2 )
     14    RETURN NUMBER
     15  IS
     16  BEGIN
     17      SELF.total := SELF.total || ',' || VALUE;
     18      RETURN ODCIConst.Success;
     19  END;
     20
     21  MEMBER FUNCTION ODCIAggregateTerminate(SELF IN string_agg_type,
     22                                         returnValue OUT varchar2,
     23                                         flags IN NUMBER)
     24    RETURN NUMBER
     25  IS
     26  BEGIN
     27      returnValue := ltrim(SELF.total,',');
     28      RETURN ODCIConst.Success;
     29  END;
     30
     31  MEMBER FUNCTION ODCIAggregateMerge(SELF IN OUT string_agg_type,
     32                                     ctx2 IN string_agg_type)
     33    RETURN NUMBER
     34  IS
     35  BEGIN
     36      SELF.total := SELF.total || ctx2.total;
     37      RETURN ODCIConst.Success;
     38  END;
     39
     40  END;
     41  /

    TYPE body created.

    SQL> CREATE OR REPLACE FUNCTION stragg(INPUT VARCHAR2)
      2    RETURN varchar2
      3  PARALLEL_ENABLE AGGREGATE USING string_agg_type;
      4  /

    FUNCTION created.

    SQL> SELECT stragg(a)
      2  FROM   t;

    STRAGG(A)
    -------------------------
    X,Y
     
    ) however such functions won't display the column header as column 1. You may need to write some PL/SQL to put the column name as the first column followed by the aggregated values afterwards. To allow us to provide a better response please post the version of Oracle you are using (four or five numbers, please ,not marketing speak such as '9i', '10g', etc.) Once we have that information we can supply a better answer to your question.
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    For Decode and Sysconnect by Solution refer the link Pivoting (Row to Column Conversion) Techniques (SQL)