Results 1 to 1 of 1
  1. #1
    Join Date
    Apr 2012
    Posts
    7

    Question Unanswered: Convert verticle table to horizontal

    Suppose I've a table like:
    Param_Name Param_Value
    Addition Code a1254
    Transmit Code t785
    Final Code f4512


    and I want a new table like:
    Addition Code Transmit Code Final Code
    a1254 t785 f4512


    I'm aware of pivot concept but which aggr function should i use to get respective string value.

    My Code:
    Code:
    CREATE OR REPLACE
    PROCEDURE SP_TRANSPOSE(
      g_ref OUT SYS_REFCURSOR)
    IS
    CURSOR param_curs is select param_name from param_list;
    param_name2 varchar(20);
    v_names  varchar2 (1000);
    v_sql   varchar2 (1000);
    
    BEGIN
    OPEN param_curs;
    LOOP
      FETCH param_curs into param_name2;
      EXIT WHEN param_curs%NOTFOUND;
      dbms_output.put_line(param_name2);
      if v_names is not null then
      v_names:=v_names||','||''''|| param_name2||'''';
      ELSE
      v_names:= ''''||param_name2||'''';
      end if;
    END LOOP;
    dbms_output.put_line(v_names);
    v_sql := 'select * from (select * from param_list)
      pivot (count (param_value) for param_name in (' || v_names || '))';
    open g_ref for v_sql;
    
    END SP_TRANSPOSE;

    Output:
    'Addition Code' 'Transmit Code' 'Final Code'
    1 1 1
    Last edited by poonam4589; 05-03-12 at 09:17.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •