Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2011
    Posts
    3

    Unanswered: Pivot transpose data for key column ?

    I want to transpose data from Source format to Target format, listed below for sample.

    Code:
    Source Format:                    
    Source table:    source_tbl1                
    column:    
      mnth  Key     a       b       c        <- column names
        01    x    xa01    xb01    xc01       <- values
        02    x    xa02    xb02    xc02
        03    x    xa03    xb03    xc03
        01    y    ya01    yb01    yc01
        02    y    ya02    yb02    yc02
        03    y    ya03    yb03    yc03
        01    z    za01    zb01    zc01
        02    z    za02    zb02    zc02
        03    z    za03    zb03    zc03
    
    Target Format:                            
    Target table:    target_tbl1                
    Column:    
        Key  a01      a02    a03      b01    b02      b03     c01     c02    c03   <- column name
        x    xa01    xa02    xa03    xb01    xb02    xb03    xc01    xc02    xc03   <- values
        y    ya01    ya02    ya03    yb01    yb02    yb03    yc01    yc02    yc03
        z    za01    za02    za03    zb01    zb02    zb03    zc01    zc02    zc03
    how to write a query for this, help

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Code:
    select [Key]
    ,a01=min(case when mnth=1 then a end)
    ,a02=min(case when mnth=2 then a end)
    ,a03=min(case when mnth=3 then a end)
    ,b01=min(case when mnth=1 then b end)
    ,b02=min(case when mnth=2 then b end)
    ,b03=min(case when mnth=3 then b end)
    ,c01=min(case when mnth=1 then c end)
    ,c02=min(case when mnth=2 then c end)
    ,c03=min(case when mnth=3 then c end)
    from source_tbl1 
    group by [Key]
    order by [Key]

  3. #3
    Join Date
    Oct 2011
    Posts
    3
    Thanks for reply..

    Here I am trying to create dynamic proc to handle any no of columns, any type of data type

    To run the proc:
    ----------------
    call transpose_col_to_row ('s_tbl', 'key1', 'x,y,z', 'mnth', 1, 3, 't_tbl');


    Code:
    create procedure transpose_col_to_row   ( IN @p_source_table_name     VARCHAR(50)   DEFAULT NULL,
                                                   IN @p_source_key_column     VARCHAR(30)   DEFAULT NULL,
                                                   IN @p_column_to_spread      LONG VARCHAR  DEFAULT NULL,
                                                   IN @p_lookup_month_column   VARCHAR(30)   DEFAULT NULL,
                                                   IN @p_opening_month         INT           DEFAULT NULL,
                                                   IN @p_closing_month         INT           DEFAULT NULL,
                                                   IN @p_target_table_name     VARCHAR(50)   DEFAULT NULL )
    begin
    --
      declare @li_month_start        int         ;  -- Local integer to hold Starting month
      declare @lv_concat_column      long varchar;  -- Local string to store concatenated column names
      declare @lv_concat_query       long varchar;  -- Local string to store select query with concatenated columns
      declare @lv_concat_value       long varchar;  -- Local string to store concatenated columns data
      declare @lv_cursor_query       long varchar;  -- Local string to store select query of distinct key values
      declare @lv_key_column_value   long varchar;  -- Local string to store hold distinct key column value(s)
      declare @lv_insert_query       long varchar;  -- Local string to store insert query to populate the target table
      declare @lv_data_value         text        ;  -- Local string to store 1 complete row values to insert
    --
    -- Replace the , in the p_column_to_spread with concatenation operator separated by comma
    -- and build the cursor query based on the distinct key values
    --
      set    @lv_concat_column = replace(@p_column_to_spread, ',' ,'||'',''||') ;
    --
      set    @lv_cursor_query = ' select  distinct ' || @p_source_key_column
                             || ' from   ' || @p_source_table_name
                             || ' where   convert(int,' || @p_lookup_month_column
                             || ')between ' || @p_opening_month || ' and ' || @p_closing_month ;
    --
    -- Declare cursor with the built cursor query select statement
    --
      declare @cur_distinct_key cursor using @lv_cursor_query ;
      open    @cur_distinct_key ;
      fetch   @cur_distinct_key into @lv_key_column_value ;
      while  @@sqlstatus = 0 -- Until successful data fetch
      loop
      --
        set   @li_month_start  = @p_opening_month ; -- Initialize the opening month for every distinct key value
        while @li_month_start <= @p_closing_month ; -- Loop till  the closing month for every distinct key value
        --
        loop
        --
        -- Build the concat query to select the key value's data for a specific month
        --
          set @lv_concat_query = ' select ' || @lv_concat_column
                              || ' from   ' || @p_source_table_name
                              || ' where  convert(int,' || @p_lookup_month_column || ')=' || @li_month_start
                              || ' and    ' || @p_source_key_column || '=''' || @lv_key_column_value || '''' ;
        --
          declare @cur_column_data cursor using @lv_concat_query ;
          open    @cur_column_data ;
          fetch   @cur_column_data into @lv_concat_value ;
        --
          while  @@sqlstatus = 0
          loop
            set     @lv_data_value = @lv_data_value || ',' || @lv_concat_value ;
            fetch   @cur_column_data into @lv_concat_value ;
          end loop;
        --
          close   @cur_column_data ;
          deallocate @cur_column_data ;
        --
          set @li_month_start = @li_month_start+1 ; -- Increment the month value to fetch the next month data for the key value
        --
        end loop ;
      --
      -- Prepare the insert statement to populate the target table with the data of a distinct key value
      --
        set @lv_insert_query = ' insert into ' || @p_target_table_name || ' values ( '''
                             || @lv_key_column_value || ''',' || substr(@lv_data_value,2) || ')' ;
      --
        execute (@lv_insert_query) ;
        set @lv_data_value = '' ;
      --
        fetch   @cur_distinct_key into @lv_key_column_value ;
      --
      end loop ;
    --
      close   @cur_distinct_key ;
      deallocate @cur_distinct_key ;
    --
      commit ;
    --
    end ;

    I am getting error ???

Posting Permissions

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