Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2004
    Posts
    64

    Unanswered: SQL to display row multiple times with count

    For Db2 V7 in z/OS

    For table that has
    col-1 col-2
    aaaa 2

    The SQL should produce

    aaaa 1
    aaaa 2

    Thanks for any help!

  2. #2
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    inner join with the same table with A.col2 <= B.col2
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  3. #3
    Join Date
    Apr 2004
    Posts
    64
    Rahul,

    I do not understand. For example the row
    aaaa 1

    doesn't even exist in table. How is inner join going to generate that? Please explain.

    Thanks.

  4. #4
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    sorry.. forgot to mention row_number() over() function

    create table abc (name varchar(10), no smallint );
    insert into abc values ('Rahul' ,5);
    select A.name , B.no from abc A,
    (select row_number() over() as no from syscat.columns ) as B
    where B.no<=A.no;


    NAME NO
    ---------- --------------------
    Rahul 1
    Rahul 2
    Rahul 3
    Rahul 4
    Rahul 5
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  5. #5
    Join Date
    Apr 2004
    Posts
    64
    Thanks Rahul!

    But, I don't think the row_number and over() functions are available in Db2 V7 in z/OS which I am using.

    But it is nice to see that in LUW it is possible now.

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You know the maximum value for "col2"? If yes, then you could do something like this (which assumes MAX(col2) = 4):
    Code:
    SELECT col1, 1
    FROM   ...
    WHERE  col2 <= 1
    UNION ALL
    SELECT col1, 2
    FROM   ...
    WHERE  col2 <= 2
    UNION ALL
    SELECT col1, 3
    FROM   ...
    WHERE  col2 <= 3
    UNION ALL
    SELECT col1, 4
    FROM   ...
    WHERE  col2 <= 4
    Alternatively, if you have recursive SQL on your platform, try this:
    Code:
    WITH max(v) AS
       ( SELECT MAX(col2) FROM ... ),
    recurs(col, colMax, currentMax) AS
       ( SELECT col1, col2, 1
         FROM   ...
         UNION ALL
         SELECT col, colMax, currentMax + 1
         FROM   recurs, max
         WHERE  colMax < currentMax )
    SELECT col, currentMax
    FROM    recurs
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you don't already have one, start by creating an integers table
    Code:
    create table integers
      (i integer not null primary key);
    insert into integers (i) values (0);
    insert into integers (i) values (1);
    insert into integers (i) values (2);
    insert into integers (i) values (3);
    insert into integers (i) values (4);
    insert into integers (i) values (5);
    insert into integers (i) values (6);
    insert into integers (i) values (7);
    insert into integers (i) values (8);
    insert into integers (i) values (9);
    now we can generate your results like this --
    Code:
    select yourtable.col1
         , integers i
      from yourtable
    inner
      join integers
        on integers.i between 1 and yourtable.col2
    order
        by yourtable.col1
         , integers i
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Apr 2004
    Posts
    64
    Thanks Stolze and R937 for those fantastic solutions!

    Stolze, I do not have recursive SQL but I think your first solution will work.

    R937, I tried out your solution and it works great!

Posting Permissions

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