2 Replies Latest reply: Jan 30, 2013 10:19 PM by NSK2KSN RSS

    couldn't get expected results from pivot

    NSK2KSN
      with t1 as 
      (
      select 'eff_date' param_name, 'mb256_type' param_type,'01-01-1970' param_value from dual
      union all
      select 'disc_date' param_name, 'mb256_type' param_type,'31-12-9999' param_value from dual
      union all
      select 'initial val' param_name, 'mb256_type' param_type,'30' param_value from dual)
      select param_name,param_type,param_value from t1;
      
      desired output:
      
      need output in a row in three different columns
      
      param_value
      01-01-1970    31-12-9999 30
      
      I tried below query
      
      SELECT *    
      FROM   (
      with t1 as 
      (
      select 'eff_date' param_name, 'mb256_type' param_type,'01-01-1970' param_value from dual
      union all
      select 'disc_date' param_name, 'mb256_type' param_type,'31-12-9999' param_value from dual
      union all
      select 'initial val' param_name, 'mb256_type' param_type,'30' param_value from dual)
      SELECT param_type, param_value,param_name
              FROM   t1
              where PARAM_TYPE = 'mb256_type')
      PIVOT  (max(param_value) AS param_value FOR (param_name) IN ('Eff_Date' AS eff_date, 'Disc_Date' AS disc_date, 'initial_ignoring' AS initial_ignoring));
      
      and am getting output as 
      
      param_type      eff_date_param_value   disc_date_param_value   initial_ignoring_param_value
      mbn256_type    <null>                                 <null>                           <null>
      
      please correct me
        • 1. Re: couldn't get expected results from pivot
          Frank Kulash
          Hi,
          NSK2KSN wrote:
          ... I tried below query
          
          SELECT *    
          FROM   (
          with t1 as 
          (
          select 'eff_date' param_name, 'mb256_type' param_type,'01-01-1970' param_value from dual
          union all
          select 'disc_date' param_name, 'mb256_type' param_type,'31-12-9999' param_value from dual
          union all
          select 'initial val' param_name, 'mb256_type' param_type,'30' param_value from dual)
          SELECT param_type, param_value,param_name
          FROM   t1
          where PARAM_TYPE = 'mb256_type')
          PIVOT  (max(param_value) AS param_value FOR (param_name) IN ('Eff_Date' AS eff_date, 'Disc_Date' AS disc_date, 'initial_ignoring' AS initial_ignoring));
          None of the literals in the PIVOT clause match tha actual values.
          Try:
          PIVOT  ( MAX (param_value)      AS param_value 
                       FOR (param_name) IN ( 'eff_date'      AS eff_date     
                                   , 'disc_date'     AS disc_date
                              , 'initial val'   AS initial_ignoring
                              )
                 );
          • 2. Re: couldn't get expected results from pivot
            NSK2KSN
            Thanks Frank, finally my query solved and understood how to use PIVOT Clause,

            Thanks once again.