How to separate table value?

[paispatin]

This is the first table (3 field, 5 record) :

| Name | Type | Value |
--------------------------
a1 L 400
a2 P 100
a3 P 150
a4 L 200
a5 P 300


How to make that first table value become like this (table bellow) :

| P Name | P Value | L Name | L Value |
-------------------------------------------
a2 100 a1 400
a3 150 a4 200
a5 300

So the result become separate : P at the left side and L for the right side, but it still at 1 table (with 4 field, and totaly 3 record))

Thank you in advance

 

[baltman]

There is no doubt a much easier way to do this that someone will be kind enough to post later.

Brian

In the meantime...

CREATE TABLE MyTable (name c(2), type c(1), value n(3))

INSERT INTO MyTable VALUES ([a1],[L],400)
INSERT INTO MyTable VALUES ([a2],[P],100)
INSERT INTO MyTable VALUES ([a3],[P],150)
INSERT INTO MyTable VALUES ([a4],[L],200)
INSERT INTO MyTable VALUES ([a5],[P],300)

SELECT DISTINCT 000 as Counter,NAME as P_Name,value as P_Value ;
FROM MyTable ;
WHERE type=[P] ;
INTO TABLE temp1 

REPLACE ALL counter WITH RECNO()

lnCnt1=RECCOUNT()

SELECT DISTINCT 000 as Counter,NAME as L_Name,value as L_Value ;
FROM MyTable ;
WHERE type=[L] ;
INTO TABLE temp2

REPLACE ALL counter WITH RECNO()

lnCnt2=RECCOUNT()

IF lnCnt1>lnCnt2
 SELECT temp2
  FOR lnCounter = lnCnt2 TO lnCnt1
    APPEND BLANK
    REPLACE counter WITH RECNO()
  ENDFOR
ELSE
 SELECT temp1
  FOR lnCounter = lnCnt1 TO lnCnt2
    APPEND BLANK
    REPLACE counter WITH RECNO()
  ENDFOR
ENDIF

SELECT dist * from temp1 a,temp2 b WHERE a.counter=b.counter nowait

 

[rgbean]

It's unclear to me what "links" the two sides together. Is it simply the physical record order in the first table? What happens if you have 3 Ls or Ps together - is it the case like your 3rd record in the new table that there will simply be missing data in the record?

Rick

 

[paispatin]

Baltman, after I try your code, it work like what I need.

Other solusion welcome.
Thank you very much.

 

[DSummZZZ]

If it is simply physical order like Rick mentioned, you could do something like:

SELECT name AS L_NAME, value AS L_VALUE ;
   FROM OldTable ;
   WHERE type = "L";
   INTO CURSOR l_temp
SELECT name AS P_NAME, value AS P_VALUE ;
   FROM OldTable ;
   WHERE type = "P";
   INTO CURSOR p_temp

GO TOP

USE NewTable IN 0
SELECT NewTable
APPEND FROM l_temp
SCAN
   SELECT p_temp
   SCATTER MEMVAR
   SELECT NewTable
   GATHER MEMVAR
ENDSCAN

Don't know if it's any easier or prettier than baltman's, but there's always different opinions I reckon.


-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm