SQL to display row multiple times with count

xamar · #1 (**permalink**) 02-18-07, 23:41

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!

rahul_s80 · #2 (**permalink**) 02-18-07, 23:44

inner join with the same table with A.col2 <= B.col2

xamar · #3 (**permalink**) 02-19-07, 00:17

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.

rahul_s80 · #4 (**permalink**) 02-19-07, 00:54

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

xamar · #5 (**permalink**) 02-19-07, 01:05

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.

stolze · #6 (**permalink**) 02-21-07, 04:03

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

r937 · #7 (**permalink**) 02-21-07, 06:41

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

xamar · #8 (**permalink**) 02-23-07, 20:10

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!

Internet Services

Web Development

Digital Marketing

Consumer Tech