Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Transpose records to columns.

Status
Not open for further replies.
erp84

erp84

IS-IT--Management
Mar 11, 2013
35
US
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?
 
Sort by date Sort by votes
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

 
Upvote 0 Downvote
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:

Code: 
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:

Code: 
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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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:

Code: 
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
 
Upvote 0 Downvote
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
And I boiled both down to your case:

Code: 
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.
 
Upvote 0 Downvote
Pretty much the same as Tamar's suggestion above...

Code: 
* --- 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

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

Tamar
 
Upvote 0 Downvote
Sweet! Got working, thanks guys!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

ravicoder
  • Locked
  • Question
Merging Columns in a grid
Replies
3
Views
272
Mike Gagnon
Mike Gagnon
tsibert
  • Locked
  • Question
Randomly displaying single or multiple records 2
2
Replies
25
Views
2K
Piotr Czekalski
Piotr Czekalski
Filip Brnic
  • Locked
  • Question
Search records by typing (grid) 2
Replies
4
Views
216
mjcmkrsr
mjcmkrsr
Filip Brnic
  • Locked
  • Question
Detail 1 band is to large to fit
Replies
5
Views
474
WilsonKWChan
WilsonKWChan
AndrewMozley
  • Locked
  • Question
SQL : Searching for records with an exact field value 1
Replies
7
Views
135
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top