How to replace VALUES IN UNPIVOT QUERY

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • lenygold via DBMonster.com

    How to replace VALUES IN UNPIVOT QUERY

    I am running this query at home PC with DB2 EXPRESS 9.5C, but at work on
    mainframe i don't
    have option VALUES.

    What is the best way to UNPIVOT without using VALUES?
    Thank's in advance.
    Lenny G.
    SELECT S.year, Q.quarter, Q.sales
    FROM sales1 AS S,
    TABLE(VALUES(1, S.q1),
    (2, S.q2),
    (3, S.q3),
    (4, S.q4))
    AS Q(quarter, sales);

    YEAR QUARTER SALES
    ----------- ----------- -----------
    2005 1 20
    2005 2 30
    2005 3 15
    2005 4 10
    2006 1 35
    2006 2 31
    2006 3 29
    2006 4 19
    2007 1 41
    2007 2 43
    2007 3 38
    2007 4 25

    12 record(s) selected.

    --
    Message posted via http://www.dbmonster.com

  • Serge Rielau

    #2
    Re: How to replace VALUES IN UNPIVOT QUERY

    lenygold via DBMonster.com wrote:
    I am running this query at home PC with DB2 EXPRESS 9.5C, but at work on
    mainframe i don't
    have option VALUES.
    >
    What is the best way to UNPIVOT without using VALUES?
    Thank's in advance.
    Lenny G.
    SELECT S.year, Q.quarter, Q.sales
    FROM sales1 AS S,
    TABLE(VALUES(1, S.q1),
    (2, S.q2),
    (3, S.q3),
    (4, S.q4))
    AS Q(quarter, sales);
    The only way I can think of is to do:
    (SELECT 1, S.q1 FROM SYSIBM.SYSDUMMY 1
    UNION ALL
    .....) AS Q(quarter, sales)

    Cheers
    Serge

    --
    Serge Rielau
    DB2 Solutions Development
    IBM Toronto Lab

    Comment

    Working...