Transpose records to columns.

[erp84]

Here is what my data looks like:

ID | LOCATION
123, A1
123, Z8
123, Q3
123, B1
123, N4
124, A6
124, A7
124, A8
124, Z0
124, Q5

I need this:

ID | LOCATION1 | LOCATION2 | LOCATION3 | LOCATION4 | LOCATION5 |
123, A1, Z8, Q3, B1, N4
124, A6, A7, A8, Z0, Q5

I tried to run this query with GENXTAB but I end up with too many unique locations. I know I'll never end up with more than 10 locations per ID.

What would be the suggested method to use, since my initial plan to use genxtab is a bust?

 

[JRB-Bldr]

The first thing I would do would be to suggest that you NOT approach your data 'architecture' as you are suggesting.

You should work to NORMALIZE your data in the data tables and your suggested approach would not be that.

Depending on the complexity of your content, an Example might be:
Table 1 - All 'basic' information regarding the ID's (ID #, name, address, etc.)
Table 2 - All 'basic' information regarding the Locations (Location ID #, address, etc.)
Table 3 - Items in various Locations and which ID they belong to.

Good Luck,
JRB-Bldr

 

[Dan Freeman]

Do you really have more than 253 locations? If so, a VFP cross tab won't get it. But you also won't get a cross tab with only two columns. It takes three, exactly.

To derive your third column, add a count:

Select ID, LOCATION, Count(id) Group by 1,2 Order by 1,2 Into Cursor MyCursor
Do (_GENXTAB)

You could, of course, flip the first two columns if you have fewer IDs than Locations.

Lacking that, go outside VFP:

COPY TO tempfile.txt DELIMITED

Bring the resulting file into Excel and use its cross tab function. Excel can handle more columns than VFP, IIRC.

 

[erp84]

The cross tab probably wouldn't have worked for what I'm trying to do anyway. I'm just trying to load a cursor with my data in this format.

What I ultimately need is one record per ID, and for each location for that ID to have its own column. Doesn't matter what order they're in, so if ID# 789 only has one location, it would drop into LOCATIOIN1 column.

I have approx 10,0000 IDs, totaling about 40,000 records, and several hundred locations. A single ID wouldn't exceed 10 different locations.

 

[TamarGranor]

I presume this is for reporting or exporting or something like that. If not, if you want to maintain it this, think again.

I don't think you can do this with VFP's SQL alone, but a combination of SQL and Xbase should work. In fact, if, as your sample suggests, the data is already sorted by ID and in the order you want, just Xbase will do. Something like this:

LOCAL iColumn, iCurrentID, cField

CREATE CURSOR csrResult (ID I, LOCATION1 C(2), LOCATION2 C(2), LOCATION3 C(2), ;
                         LOCATION4 C(2), LOCATION5 C(2), LOCATION6 C(2), ;
                         LOCATION7 C(2), LOCATION8 C(2), LOCATION9 C(2), ;
                         LOCATION10 C(2))

SELECT YourData
iCurrentID = -1
SCAN
  IF YourData.ID <> m.iCurrentID
     * Start a new record
     INSERT INTO csrResult (ID) VALUES (YourData.ID)
     iColumn = 1
  ENDIF

  IF iColumn > 10
     * Problem! Too many for this ID.
     * Do whatever you think is appropriate.
  ELSE
    cField = "Location" + TRANSFORM(m.iColumn)
    REPLACE (m.cField) WITH YourData.Location
    iColumn = m.iColumn + 1
  ENDIF
ENDSCAN

If your data isn't already sorted as I described, you'll need a query first to get it that way.

Tamar

 

[Olaf Doschke]

I think genxtab won't work out, as you say you have several 100 locations, you need the third column as dan said, but it shouldn't be the count, it should be a sequential number, which resets to 1 for each ID and it should be the second field, this means sequential numbering within groups.

And thread184-1611728 shows how to do sequential numbering within groups, in your case groups of the same ID.

That thread points to

http://www.databasejournal.com/feat...beringCounting-of-Records-with-SQL-Server.htm

And I boiled both down to your case:

Select t0.Id, locationorder, t0.location From yourtable as t0;
   left Join ;
   (Select Count(*) As locationorder, t1.Id, t1.location From yourtable as t1 ;
   Left Join yourtable as t2 On t1.Id=t2.Id  And t1.location>=t2.location ;
   Group By t1.Id, t1.location) as t3;
   On t0.Id = t3.Id And t0.location = t3.location ;
   Order By 1, 2;
   into Cursor curTest Readwrite

Do (_genxtab)

In the end curTest will not exist anymore, instead you have your final result in XTab and fields will be named N_1 to N_10, but that shouldn't matter much.

Considering, that crosstab code is a lot more code than Tamars, you should take her solution. It only misses to set iCurrentID = YourData.ID within the first IF statement, iCurrentID has to update, or you always start a new record, as all YourData.IDs are <> -1.

Bye, Olaf.

 

[JRB-Bldr]

Pretty much the same as Tamar's suggestion above...

* --- Create Recipient Cursor ---
CREATE CURSOR RptData;
   (ID I,;
   LOCATION1 C(10);
   LOCATION2 C(10);
   LOCATION3 C(10);
   LOCATION4 C(10);
   LOCATION5 C(10);
   LOCATION6 C(10);
   LOCATION7 C(10);
   LOCATION8 C(10);
   LOCATION9 C(10);
   LOCATION10 C(10))

* --- Get List of Unique ID's ---
SELECT DISTINCT ID;
  FROM YourData;
  INTO CURSOR IDList 

SELECT IDList
SCAN
   nID = IDList.ID
   
   * --- Add This New ID To Recipient Cursor ---
   SELECT RptData
   APPEND BLANK
   REPLACE ID WITH nID

   SELECT *;
     FROM YourData;
     WHERE ID = nID;
     INTO CURSOR ThisIDsLocs
   
   nLocCntr = 0
   SELECT ThisIDsLocs
   SCAN
      * --- Define This Location Field Name ---
      nLocCntr = nLocCntr + 1
      cLocFld = "LOCATION" + STR(nLocCntr,1)

      * --- Populate This Location Field As Needed ---
      cLocVal = ThisIDsLocs.Location
      REPLACE &cLocFld WITH cLocVal
   ENDSCAN   
   USE

   SELECT IDList
ENDSCAN

Good Luck,
JRB-Bldr

 

[TamarGranor]

Good catch, Olaf. That's what happens when I write code without a chance to test.

Tamar

 

[erp84]

Sweet! Got working, thanks guys!