Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Rick C. Hodgin
  Where is Rick C. Hodgin?
 Indianapolis
 Indiana - United States
 Rick C. Hodgin
 To: prasanna kunder
  Where is prasanna kunder?
 mumbai
 India
 prasanna kunder
 Tags
Subject: RE: How To Do
Thread ID: 365534 Message ID: 365546 # Views: 49 # Ratings: 1
Version: Visual FoxPro 9 Category: Grids
Date: Thursday, December 27, 2012 10:14:17 PM         
   


> >
> >
> >
> > Something like:
> >
> > * Grab the unique listing
> > SELECT DISTINCT desc ;
> >     FROM myTable ;
> >     INTO CURSOR cDescList
> > 
> > * Build it (if it gets to be too big, you'll have to do it programmatically)
> > lcSql = "SELECT flatNo,name,"
> > SCAN
> >     lcSql = lcSql + ",SUM(IIF(desc = '" + cDescList.desc + "', amt, 00000.00)) as amt" + ALLTRIM(STR(RECNO('cDescList'),3,0))
> > ENDSCAN
> > 
> > * Append the tail
> > lcSql = lcSql + " FROM myTable GROUP BY 1, 2 INTO CURSOR c_output"
> > 
> > * Execute it
> > &lcSql
> > 

> >
> > Best regards,
> > Rick C. Hodgin
>
> sir
> i didn't get this one
> ALLTRIM(STR(RECNO('cDescList'),3,0))
> it will skip record in cDescList table



It's creating unique named fields, as "amt1" for first description, "amt2" for second, "amt3" for third, all the way up to "amtN" for the Nth description.

The source code needs a trailing parenthesis as I have corrected above.

You could also shorten the length of the desc field by getting the record count of the first pass full field-length, and then iterating through your distinct list downward until it changes. Once it changes, go back up one character and you have the minimum length required to be unique.

SELECT DISTINCT desc ;
    FROM myTable ;
    INTO CURSOR cDescFullList

* Iterate to find out the minimum unique length required
FOR lnI = LEN(desc)-1 TO 1 STEP -1
    SELECT DISTINCT LEFT(desc, lnI) AS desc ;
        FROM cDescFullList ;
        INTO CURSOR cDescListCandidate
    IF RECCOUNT('cDescListCandidate') != RECCOUNT('cDescFullList')
        * We've gone past our minimum unique requirement
        EXIT
    ENDIF
NEXT

* When we get here, adding one to the lnI value gives us our minimum unique requirement
SELECT DISTINCT LEFT(desc, lnI+1) AS desc ;
    FROM cDescFullList ;
    INTO CURSOR cDescList


Best regards,
Rick C. Hodgin

ENTIRE THREAD

How To Do Posted by prasanna kunder @ 12/27/2012 8:08:28 PM
RE: How To Do Posted by Rick Hodgin @ 12/27/2012 9:21:35 PM
RE: How To Do Posted by prasanna kunder @ 12/27/2012 9:28:20 PM
RE: How To Do Posted by Rick Hodgin @ 12/27/2012 9:38:18 PM
RE: How To Do Posted by Rick Hodgin @ 12/27/2012 9:49:22 PM
RE: How To Do Posted by prasanna kunder @ 12/27/2012 9:58:56 PM
RE: How To Do Posted by Rick Hodgin @ 12/27/2012 10:14:17 PM
RE: How To Do Posted by Anders Altberg @ 12/28/2012 2:21:03 AM
RE: How To Do Posted by prasanna kunder @ 12/29/2012 10:41:30 AM