Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2011
    Posts
    4

    Unanswered: Display months names as column names in between dates

    Hi

    i want to display the month names as columns in between date.
    for example: my input is 01/3/2011 and 04/3/2011. column names like :
    Employe id januarysalaray february salaray march salaray april salaray
    121 2000 3000 4000 50000

    i want display the above order.

    i am new in db2,Please help me somebody for that query.

    Thanks.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You can't change column names by the result of query,
    if you didn't execute dynamically the SQL statements.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    for example: my input is 01/3/2011 and 04/3/2011. column names like :
    Employe id januarysalaray february salaray march salaray april salaray
    121 2000 3000 4000 50000
    You may want to use PIVOT technique.
    A good example of PIVOT was in SQL on Fire! Part 1 http://sirdug.org/downloads/SQLonFire_1_SirDUG.pdf

    I wrote:
    You can't change column names by the result of query,
    if you didn't execute dynamically the SQL statements.
    But, an alternative may be to put column names in first row of results.

    Example: also using PIVOT technique.
    results:
    Code:
    ------------------------------------------------------------------------------
    
                                                                                                                     
    ----------- ---------------- ---------------- ---------------- ---------------- ---------------- ----------------
    Employee id January salary   February salary  March salary     April salary     May salary       June salary     
    121         2000             3000             4000             5000             -                -               
    
      2 record(s) selected.
    query:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     employee_salary
    (employee_id , salary , salary_date) AS (
    VALUES
      (121 ,  3500 , DATE('2010-09-03') )
    , (121 ,  2500 , DATE('2010-10-04') )
    , (121 ,  1500 , DATE('2010-11-05') )
    , (121 ,  1000 , DATE('2010-12-10') )
    , (121 ,  2000 , DATE('2011-01-03') )
    , (121 ,  3000 , DATE('2011-02-03') )
    , (121 ,  4000 , DATE('2011-03-03') )
    , (121 ,  5000 , DATE('2011-04-03') )
    , (121 ,  4500 , DATE('2011-05-03') )
    )
    SELECT CASE GROUPING(employee_id)
           WHEN 0 THEN
                CHAR(employee_id)
           ELSE 'Employee id'
           END  AS " "
         , CASE GROUPING(employee_id)
           WHEN 0 THEN
                MAX( CASE
                     WHEN MONTH(salary_date) = MONTH(from_date) THEN
                          CHAR(salary)
                     END )
           ELSE MAX( SUBSTR( MONTHNAME(from_date)||' salary' , 1 , 16 ) )
           END  AS " "
         , CASE GROUPING(employee_id)
           WHEN 0 THEN
                MAX( CASE
                     WHEN MONTH(salary_date) = MONTH(from_date + 1 MONTH) THEN
                          CHAR(salary)
                     END )
           ELSE MAX( SUBSTR( MONTHNAME(from_date + 1 MONTH)||' salary' , 1 , 16 ) )
           END  AS " "
         , CASE GROUPING(employee_id)
           WHEN 0 THEN
                MAX( CASE
                     WHEN MONTH(salary_date) = MONTH(from_date + 2 MONTHs) THEN
                          CHAR(salary)
                     END )
           ELSE MAX( SUBSTR( MONTHNAME(from_date + 2 MONTH)||' salary' , 1 , 16 ) )
           END  AS " "
         , CASE GROUPING(employee_id)
           WHEN 0 THEN
                MAX( CASE
                     WHEN MONTH(salary_date) = MONTH(from_date + 3 MONTHs) THEN
                          CHAR(salary)
                     END )
           ELSE MAX( SUBSTR( MONTHNAME(from_date + 3 MONTH)||' salary' , 1 , 16 ) )
           END  AS " "
         , CASE GROUPING(employee_id)
           WHEN 0 THEN
                MAX( CASE
                     WHEN MONTH(salary_date) = MONTH(from_date + 4 MONTHs) THEN
                          CHAR(salary)
                     END )
           ELSE MAX( SUBSTR( MONTHNAME(from_date + 4 MONTH)||' salary' , 1 , 16 ) )
           END  AS " "
         , CASE GROUPING(employee_id)
           WHEN 0 THEN
                MAX( CASE
                     WHEN MONTH(salary_date) = MONTH(from_date + 5 MONTHs) THEN
                          CHAR(salary)
                     END )
           ELSE MAX( SUBSTR( MONTHNAME(from_date + 5 MONTH)||' salary' , 1 , 16 ) )
           END  AS " "
     FROM  employee_salary
         , (VALUES ( DATE('1/3/2011') , DATE('04/3/2011') )
           ) input(from_date , to_date)
     WHERE salary_date
           BETWEEN from_date AND to_date
     GROUP BY
           ROLLUP ( employee_id )
     ORDER BY
           GROUPING(employee_id) DESC
         , employee_id           ASC
    ;
    If input was '8/3/2010' and '04/3/2011', then
    Code:
    ...
    ...
     FROM  employee_salary
         , (VALUES ( DATE('8/3/2010') , DATE('04/3/2011') )
           ) input(from_date , to_date)
     WHERE salary_date
           BETWEEN from_date AND to_date
     GROUP BY
           ROLLUP ( employee_id )
     ORDER BY
           GROUPING(employee_id) DESC
         , employee_id           ASC
    ;
    ------------------------------------------------------------------------------
    
                                                                                                                     
    ----------- ---------------- ---------------- ---------------- ---------------- ---------------- ----------------
    Employee id August salary    September salary October salary   November salary  December salary  January salary  
    121         -                3500             2500             1500             1000             2000            
    
      2 record(s) selected.
    Last edited by tonkuma; 07-04-11 at 12:55. Reason: Replace ", CASE WHEN GROUPING(employee_id) = 0 THEN" to ", CASE GROUPING(employee_id) WHEN 0 THEN"

Posting Permissions

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