Restructure column

[i12hvfun]

Hi All,
how can I transform the structure of T1 to T2?

T1
date custid qty && these are the field name
01/01/01 123 5
01/01/01 234 6
01/01/01 345 7
....
....
12/01/01 123 51
12/01/01 234 61


T2
date 123 234 345 ..... ..... ..... &&field name
01/01/01 5 6 7 ..... ..... .....
01/02/01 .....
......
12/01/01 51 61 0 0 0 0

thanks

 

[baltman]

lnSTRICTDATE=SET("STRICTDATE")
SET STRICTDATE TO 0

CREATE TABLE T1 (OrderDate d, custid n(3), qty n(3))
INSERT INTO T1 VALUES ({01/01/01},123,5)
INSERT INTO T1 VALUES ({01/01/01},234,6)
INSERT INTO T1 VALUES ({01/01/01},345,7)
INSERT INTO T1 VALUES ({12/01/01},123,51)
INSERT INTO T1 VALUES ({12/01/01},234,61)

SELECT OrderDate,;
sum(IIF(custid=123,qty,0000)) as Cust123Qty,;
sum(IIF(custid=234,qty,0000)) as Cust234Qty,;
sum(IIF(custid=345,qty,0000)) as Cust345Qty ;
GROUP BY OrderDate FROM T1 NOWAIT

DROP TABLE T1
SET STRICTDATE TO &lnSTRICTDATE

Brian

 

[Mike Lewis]

i12hvfun,

Here's a more general way of doing it. Note that I haven't tested this. I've also made some assumptions about the actual field names in T1 (Date isn't a very good field name, for example.)

* Get all cust IDs into a cursor
SELECT DISTINCT CustID FROM T1 INTO CURSOR Temp

* Start a CREATE TABLE command
lcCmd = "CREATE TABLE T2 (T2_Date D, "

* Build the CREATE TABLE command
SELECT Temp
SCAN
  lcCmd = lcCmd + "T2_" + TRANSFORM(Temp.CustID) + " I, "
ENDSCAN

* Replace final comma with closing paren
lcCmd = LEFT(lcCmd,LEN(lcCmd)-2) + ")"

* Execute the CREATE TABLE command
&lcCmd

* We now have table T2. Populate it from T1
SELECT T1
SCAN
  SELECT T2
  REPLACE T2_Date WITH T1.T1_Date
  lcID = "T2_" + TRANSFORM(T1.CustID)
  REPLACE &lcID WITH T1.Qty
  SELECT T1
ENDSCAN 

* Show the result
SELECT T2
BROWSE

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[i12hvfun]

thanks Mike!