Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Anders Altberg
  Where is Anders Altberg?
 Uppsala
 Sweden
 Anders Altberg
 To: CJ Wiley
  Where is CJ Wiley?
 Hideaway
 Texas - United States
 CJ Wiley
 Tags
Subject: RE: Row to Column?
Thread ID: 310479 Message ID: 310681 # Views: 35 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Thursday, June 23, 2011 12:31:11 PM         
   


> >
> >
SELECT fielda as fieldx FROM Mytable ;
> > UNION SELECT fieldb FROM Mytable ;
> > UNION SELECT fieldC FROM Mytable ;
> > INTO CURSOR Mytable2

> >
> > UNION removes duplicates. UNION ALL does not.
> >
> > -Anders
>
>
> I tried that but it didn't put all field into one column (fieldx). Thank you for the suggestion. I had never used union before.

No, it doesn't put duplicte values into the result:

SELECT fielda as fieldx FROM Mytable ;
 UNION ALL SELECT fieldb FROM Mytable ;
 UNION ALL SELECT fieldC FROM Mytable ;
 INTO CURSOR Mytable2


That will put all rows including duplicate values into Mycursor2.
If you need to know where the values come from you need to select more columns.
SELECT 'A' AS source, fieldA AS fieldX FROM Mytable UNION ALL ;
SELECT 'B', fieldB FROM Mytable UNION ALL ;
SELECT 'C', fieldC FROM Mytable  ;
ORDER BY 1,2 ;
INTO CURSOR MyTable2

-Anders

ENTIRE THREAD

Row to Column? Posted by CJ Wiley @ 6/21/2011 8:19:56 PM
RE: Row to Column? Posted by Mike Yearwood @ 6/21/2011 8:43:39 PM
RE: Row to Column? Posted by CJ Wiley @ 6/22/2011 4:21:59 AM
RE: Row to Column? Posted by Anders Altberg @ 6/22/2011 12:05:21 AM
RE: Row to Column? Posted by CJ Wiley @ 6/22/2011 4:21:18 AM
RE: Row to Column? Posted by Anders Altberg @ 6/23/2011 12:31:11 PM