Display months names as column names in between dates

nsrinusetty · #1 (**permalink**) 07-01-11, 13:11

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.

tonkuma · #2 (**permalink**) 07-01-11, 21:56

You can't change column names by the result of query,
if you didn't execute dynamically the SQL statements.

tonkuma · #3 (**permalink**) 07-04-11, 11:47

Quote:

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:

Quote:

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech