Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2004
    Location
    Frankfurt
    Posts
    26

    Unanswered: How to pivot dates into columns?

    Hi everyone,

    at the risk of annoying everyone, I have a question.

    How can I pivot date values from rows to columns?

    I have already used sum(case when .... then 1 else 0 end) for example to total up rows into columns. The problem with dates is that I don't know how many various dates per customer I may have in total (could be 1 row, could be several thousand).

    Ok, an example:

    customer contract valid_from valid_to
    123456 12 01.01.2003 13.01.2003
    123456 12 13.01.2003 01.04.2003
    123456 12 05.04.2003 31.12.2003
    123456 12 01.01.2004 31.12.2004

    in the above example, I am only interested in seeing the contiguous periods
    that the contract was opened (ie. 01.01.2003 thru 01.04.2003, and then 05.04.2003 thru 31.12.2004)

    Yeah, I know - I should use a cursor within a COBOL program or something like that - but I wanna know if it's possible using only SQL. It's probly a good thing that SQL isn't recursive.

    Personally, I hate using valid-from and valid-to logic (probably because I've had to program endless programs in COBOL to handle the logic).

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    SQL can be used recursively. Try this for an example:

    with t1 (num) as (select 1 from sysibm.sysdummy1 union all select num+1 from t1 where num < 100) select * from t1

    Also check out the section in this free book on recursion:
    http://ourworld.compuserve.com/homep...l/HTM_COOK.HTM


    What you want can probably be done with straight SQL.
    If you get it working, please post your solution. I have seen other questions
    similar to this.

    Andy

  3. #3
    Join Date
    Dec 2004
    Location
    Frankfurt
    Posts
    26
    hi,

    thanks for the response - I tried the example and received error messages - apparently the DBA's don't wanna let people run recursive stuff. It would probably be easier to write a cobol prog than to convince the dba's to change the settings.

    I originally tried it in QMF (yeah, I know, QMF is for wimps), then tried going into spufi and got the following message:
    SPUFI SSID: DB32
    +-----------------------------------------------------------------------------+
    |, DSNESP06 ,|
    |, DSNE345I WARNING: DB2 DATA CORRUPTION CAN RESULT ,|
    |, FROM THIS SPUFI SESSION BECAUSE THE ,|
    |, CCSID USED BY THE TERMINAL IS NOT THE ,|
    |, SAME AS THE CCSID USED BY SPUFI. ,|
    |, ,|
    |, - TERMINAL CCSID: ,1141 ,|
    |, - SPUFI CCSID: ,273 ,|
    |, NOTIFY THE DB2 SYSTEM ADMINISTRATOR. ,|
    |, ,|
    |, PRESS: ENTER to continue ,|
    |, END to exit SPUFI ,|
    |, ,|
    |, ,|
    |, ,|
    |, ,|
    |, ,|
    |, ,|
    |, ,|
    |, ,|
    |, F1=HELP F2=SPLIT F3=END F4=RETURN F5=RFIND F6=RCHANGE ,|
    |, F7=UP F8=DOWN F9=SWAP F10=LEFT F11=RIGHT ,|
    +-----------------------------------------------------------------------------+

    Well, I'm gonna keep trying to see what I can come up with....any other replies would be appreciated.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You did not mention which Version of DB2 and OS you are using. I assume from your response that it is OS/390. Recursion may not work there. What I gave you works under DB2 for LUW.

    Andy

  5. #5
    Join Date
    Dec 2004
    Location
    Frankfurt
    Posts
    26
    hi,

    yeah, ooops - i did forget to mention platform, etc.... DB2 V7R1 on os/390....ISPF 5.2 etc etc

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Recursion on os/390 is available only from v8 ..
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    A nice intriguing problem.

    Code:
    CREATE TABLE MYTABLE (
    	CUSTOMER	CHAR(1) NOT NULL,
    	CONTRACT	SMALLINT NOT NULL,
    	VALID_FROM	DATE NOT NULL,
    	VALID_TO	DATE NOT NULL,
    	CONSTRAINt PK_MYTABLE PRIMARY KEY (CUSTOMER, CONTRACT, VALID_FROM)
    );
    
    INSERT INTO MYTABLE VALUES ('A', 1, '01/01/2000', '01/02/2001'); --
    INSERT INTO MYTABLE VALUES ('A', 1, '02/02/2001', '01/04/2001');
    INSERT INTO MYTABLE VALUES ('A', 1, '02/04/2001', '01/12/2001');
    INSERT INTO MYTABLE VALUES ('A', 1, '02/12/2001', '01/02/2002');
    INSERT INTO MYTABLE VALUES ('A', 1, '01/03/2002', '01/04/2002'); --
    INSERT INTO MYTABLE VALUES ('A', 1, '02/04/2002', '01/02/2003');
    INSERT INTO MYTABLE VALUES ('A', 1, '02/02/2003', '01/12/2004');
    INSERT INTO MYTABLE VALUES ('A', 2, '01/03/2002', '01/04/2002'); --
    INSERT INTO MYTABLE VALUES ('A', 2, '02/04/2002', '01/02/2003');
    INSERT INTO MYTABLE VALUES ('B', 1, '01/01/2000', '01/02/2001'); --
    INSERT INTO MYTABLE VALUES ('B', 1, '02/02/2001', '01/04/2001');
    INSERT INTO MYTABLE VALUES ('B', 1, '02/04/2001', '01/12/2001');
    INSERT INTO MYTABLE VALUES ('B', 1, '02/12/2001', '01/02/2002');
    INSERT INTO MYTABLE VALUES ('C', 1, '02/12/2001', '01/02/2002'); --
     (dates are in DD//MM/YYYY format)
    
    The result should be:
    	A	1	01/01/2000	01/02/2002
    	A	1	01/03/2002	01/12/2004
    	A	2	01/03/2002	01/02/2003
    	B	1	01/01/2000	01/02/2002
    	C	1	02/12/2001	01/02/2002
    This is the recursive part:


    Code:
    WITH RPL (customer, contract, valid_from, valid_to, iter) AS
    (
    -- Insert all records from MYTABLE when for that customer/contract 
    -- combination, the valid_from is not preceded by another contract-period 
    -- (i.e. it is the first record of 1 or more consecutive contract periods). 
    -- Marked with "--" after the INSERT statements.
    
    SELECT T1.customer, T1.contract, T1.valid_from, T1.valid_to, 0
    FROM MYTABLE T1
    WHERE NOT EXISTS (SELECT 1
    		FROM MYTABLE T2
    		WHERE T1.customer = T2.customer AND
    			T1.contract = T2.contract AND
    			T1.valid_from <> T2.valid_from AND
    			T1.valid_from = T2.valid_to + 1 DAY
    		)
    
    UNION ALL
    
    -- make a new record with the consecutive valid_to for that 
    -- customer/contract-combination, until there is no consecutive 
    -- mytable.valid_to to be found. You can check iter to find out how many 
    -- iterations were necessary.
    
    SELECT RPL.customer, RPL.contract, RPL.valid_from, MYTABLE.valid_to, RPL.iter+1
    FROM RPL, MYTABLE
    WHERE MYTABLE.customer = RPL.customer AND
    	MYTABLE.contract = RPL.contract AND
    	MYTABLE.valid_from = RPL.valid_to + 1 DAY
    )
    -- Get those records per customer/contract/valid_from-combination 
    -- with the highest valid_to
    
    SELECT customer, contract, valid_from, max(valid_to)
    FROM RPL
    GROUP BY customer, contract, valid_from
    ORDER BY customer, contract, valid_from
    ;
     
    The result:
    A;1;2000-01-01;2002-02-01;
    A;1;2002-03-01;2004-12-01;
    A;2;2002-03-01;2003-02-01;
    B;1;2000-01-01;2002-02-01;
    C;1;2001-12-02;2002-02-01;
    It was fun solving this one

    Wim
    Last edited by Wim; 12-15-04 at 14:05.

Posting Permissions

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