Best way to create a table

[lenygold via DBMonster.com]

I need to create 101 columns table and populate it with following information:
RN,C1,C2,C3,C4,C5................................. .........C100
0 1 0 0 0 0.............................................. 0
1 0 1 0 0 0...............................................0
2 0 0 1 0 0...............................................0
3 0 0 0 1 0...............................................0
.................................................. ..........................
100 0 0 0 0 .................................................1

Where RN is row number.
1 is always in RN + 1 Position
What is the best way to do it to reduce manual typing.
Thank's in advance.
Leny G.

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

[yongleig]

On Sep 15, 8:36*pm, "lenygold via DBMonster.com" <u41482@uwewrote:

I need to create 101 columns table and populate it with following information:

RN,C1,C2,C3,C4,C5................................. .........C100 *
0 * * 1 * 0 * *0 * 0 * 0............................................... 0
1 * * 0 * 1 * *0 * 0 * 0................................................0
2 * * 0 * 0 * *1 * 0 * 0................................................0
3 * * 0 * 0 * *0 * 1 * 0................................................0
.................................................. ..........................
100 0 * 0 * *0 * *0 *................................................. .1

Where RN is row number.
1 is always in RN + 1 Position
What is the best way to do it to reduce manual typing.
Thank's in advance.
Leny G.

--
Message posted viahttp://www.dbmonster.com

You need to create a stored procedure to compile a dynamic update.

First, insert 100 rows with all 0,

Second, create and run SP:
DECLARE count int default 0;
DECLARE stmt STATEMENT;
REPEAT
set count = count +1;
SET txt = 'update t set C' || char(count) || '=1 where RN =' ||
char(count)';
prepare stmt from txt;
execute stmt;
UNTIL (count >= 100)
END REPEAT;
Yonglei

[lenygold via DBMonster.com]

Thank You.
I am done with insert 100 rows with all 0.
Tommorow will try SP.

yo******@gmail.com wrote:

>I need to create 101 columns table and populate it with following information:

[quoted text clipped - 14 lines]

>--
Message posted viahttp://www.dbmonster.com

You need to create a stored procedure to compile a dynamic update.

First, insert 100 rows with all 0,

Second, create and run SP:
DECLARE count int default 0;
DECLARE stmt STATEMENT;
REPEAT
set count = count +1;
SET txt = 'update t set C' || char(count) || '=1 where RN =' ||
char(count)';
prepare stmt from txt;
execute stmt;
UNTIL (count >= 100)
END REPEAT;

Yonglei

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

[w.l.fischer]

Well, I'd just write something like this in perl:

for ($i=0; $i<=100; $i++) {
print "$i,";
for ($j=0; $j<$i; $j++) {
print "0,";
}
print "1,";
for ($j=$i+1; $j<99; $j++) {
print "0,";
}
print "0\n";
}

and then load the file... I'm still better at perl than at creating
stored procedures :-)

(Last line needs editing.)

[DA Morgan]

lenygold via DBMonster.com wrote:

I need to create 101 columns table and populate it with following information:
RN,C1,C2,C3,C4,C5................................. .........C100
0 1 0 0 0 0.............................................. 0
1 0 1 0 0 0...............................................0
2 0 0 1 0 0...............................................0
3 0 0 0 1 0...............................................0
.................................................. .........................
100 0 0 0 0 .................................................1

Where RN is row number.
1 is always in RN + 1 Position
What is the best way to do it to reduce manual typing.
Thank's in advance.
Leny G.

It is almost never a good practice to create a table such as the one
you have indicated.

Give serious consideration to thinking vertically rather than horizontally.

RN NUMBER
CNUM NUMBER
CVAL NUMBER
--
Daniel A. Morgan
University of Washington
da******@x.washington.edu (replace x with u to respond)

[ChrisC]

DA is right, but if you ignore him and create the table anyways,
extending the following for all 100 columns would be an easy all SQL
way to accomplish this in DB2:

with recurse(rn, c1, c2, c3, c4, c5, c6) as (
select * from table(values(0,1,0,0,0,0,0)) t0
union all
select rn+1, 0, c1, c2, c3, c4, c5
from recurse
where rn < 6)
select count(*) from final table(insert into your_table select * From
recurse) ft

Also, note that your example didn't quite work - you either start with
row1 c1=1, or you start as you did (row 0) and row 100 has all 0's.

-Chris