How to change fileds number using SQL statment

[chaohua wang]

Hi, Folks,

I have a table like this

leaseno name percnetage
1 edgy 100
1 ed 50
1 micale 30
2 kathy 70
2 nancy 78
3 tony 40

Can I use SQL to gneragte a table like this accorfing the above
note, the size of the field is dynamic depends on the number of records.

leasno name1 percnetage1 name2 percnetage2 name3 percnetage3
1 edgy 100 ed 50 micale 30
2 kathy 30 nancy 78 null null
3 tony 40 null null null null

Thank you

Chaohua

[Mark Spritzler]

It looks like you are trying to do a crosstab query. Usually the table would have been designed as third normal form where you would have had a one-to many relationship between lease number in one table(one side) and the other data in the other table(many side).

Crosstab queries are difficult and some Databases don't support it.

Mark

[Avi Abrami]

Chaohua,
Search the Ask Tom Web site for the term "pivot".

Good Luck,
Avi.