Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2011
    Posts
    220

    Unanswered: construct to one row

    Hi,

    DB2/Z/OS
    9.7

    Please find the query

    SELECT ID_NO,ID_NAME,ID_YEAR,ID_Q1,ID_Q2_ID_Q3_ID_Q4

    FROM BANSE_TABLE


    BANSE_TABLE
    Code:
    ID_NO        ID_NAME     ID_YEAR    ID_Q1   ID_Q2    ID_Q3   ID_Q4
    AA1          TEST         2016       21	     2        3       41
    AA1          TEST         2017       5       10       2       7
    AA1          TEST         2018       1       11       1       15
    we are passing the start year and end year to WHERE clause like

    01-02-2016 30-04-2017

    IN that given date range we have 6 quarters, so we need to populate first row year(2016) 4 quarters and the
    next row year(2017) 2 quarters ID_Q1 and ID_Q2 values from BANSE_TABLE to be GIVEN as one row


    so the query has to give the result set like
    Code:
    ID_NO        ID_NAME        ID_Q1   ID_Q2    ID_Q3   ID_Q4  ID_Q5  ID_Q6
    AA1          TEST             21     2        3       41      5     10

    Please help

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    take a look in the archives on here for recursion or pivot table. There are tons of examples.
    Dave

  3. #3
    Join Date
    May 2012
    Location
    Canberra, Australia
    Posts
    176
    Provided Answers: 6
    Andy

    "All parts should go together without forcing. You must remember that the parts you are reassembling were disassembled by you. Therefore, if you can’t get them together again, there must be a reason. By all means, do not use hammer.” — IBM maintenance manual, 1975 "

Posting Permissions

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