Table colum to line

[Lengliz]

Hi,
I want to convert the colum of my table1 to lines in table2:
when I browse my table1: (custmer c(20),Qty N(5))

(custmer) (QTY)
Custmer1 12
Custmer1 5
Custmer1 6
Custmer2 3
Custmer2 8
custmer3 1
........

NB.
every custmer have at max three lines in table1


I want to convert my table to another table2:
(custmer c(20), qty1 N(5),qty2 N(5),qty3 N(5))

(custmer) Qty1 Qty2 Qty3
custmer1 12 5 5
custmer2 3 8 0
custmer3 1 0 0
........

thank you
Mistral,

 

[TheRambler]

Hi,

Use a crosstab query.

 

[Lengliz]

Hi,
Can you give me more information about "crosstab"
thanks
mistral

 

[Dan Freeman]

How is VFP supposed to know which QTY records go in which column? Cross tab won't work until we know that.

In general, you need 3 columns:

Select custmer, (something), qty ;
  from custmer group by 1,2,3 ;
  order by 1,2,3 ;
  into cursor temp
DO (_GENXTAB)
BROWSE

In place of (something), put the data element that identifies which column the qty should belong to. (It can be a UDF that returns the proper value if need be.)

 

[TheRambler]

Of course, thanks danfreeman, I overlooked that.

Lengliz, since you need three columns, you could add one to put the data element that identifies which column qty belongs to, maybe like this:

ALTER TABLE table1 ADD COLUMN something n(1)
lcCustmerID = "0000000"
SCAN
   IF lcCustmerID <> table1.custmer
      lnCustmerCount = 1
   ELSE
      lnCustmerCount = lnCustmerCount + 1
   ENDIF
   REPLACE something WITH lnCustmerCount
ENDSCAN

Then you could run the code posted above by danfreeman.

Note table1 should be indexed on custmer field.

 

[TheRambler]

Oops! sorry Lengliz, I forgot a line.

ALTER TABLE table1 ADD COLUMN something n(1)
lcCustmerID = "0000000"
SCAN
   IF lcCustmerID <> table1.custmer
      lnCustmerCount = 1
[b]      lcCustmerID = table1.custmer[/b]
   ELSE
      lnCustmerCount = lnCustmerCount + 1
   ENDIF
   REPLACE something WITH lnCustmerCount
ENDSCAN

 

[Lengliz]

Thank You
It Works Fine