Transposing Columns to Rows

[Skylinc]

Hi all

I have a table like this:
cn_name, fcst1, fdate1, fcst2, fdate2, ... fcst12, fdate12

I would like to know how to write hte query to return a result like this:

cn_name, fcst1, fdate1
cn_name, fcst2, fdate2
...
cn_name, fcst12, fdate12


At first it looks like i can use UNION ALL, but if I try that will the 12 resulting queries, I get an error message saying "too many unions".

Is there any way of transposing those columns to rows? Does Foxpro have a PIVOT equivelant that I can use? I'm running the query from VB.Net Code, and would prefer not running 12 queries.

Any help would be appreciated!

 

[MrDataGuy]

I suggest that you look into using the VFPxTab.Prg (A util program that comes with VFP) This program may be what you need.

Another option:

http://vfpx.codeplex.com/wikipage?title=FoxCharts Tools

Look at the GetChartDataFromRows PRG

Lion Crest Software Services
Anthony L. Testi
President

http://www.LionCrestSoftwareServices.com

 

[JRB-Bldr]

"Does Foxpro have a PIVOT equivelant"

In a word - No.

Since you are doing your work in VB.Net and not VFP, I cannot help you with your code.

However if you were doing the work in VFP there are a number of us who can assist you a number of ways to approach this with your code.

Good Luck,
JRB-Bldr

 

[JRB-Bldr]

MrDataGuy - He says that he is doing this work in VB.Net.
As such neither VFPxTab.Prg nor GetChartDataFromRows PRG would not be code that he could/would execute in VB.Net.

I don't know about VB.Net's capability to Automate Excel, but if it could work as well as VFP (personally in doubt) he could export the VFP data to Excel and then do his row/column transposition there and then Export it from Excel to something he could use.

I know that's pretty much a kludge approach, but hey he's not working in VFP, so maybe that all that's available to him.

Skylinc - Good Luck,
JRB-Bldr

 

[Mike Lewis]

Slyink, you said "At first it looks like i can use UNION ALL, but if I try that will the 12 resulting queries, I get an error message saying "too many unions"."

"Too many unions" is not a VFP error message. VFP does not impose a limit on the number of unions in a SELECT.

Perhaps it's something in VB.Net that imposes that limit (although I can't see why it should). If so, have you considered breaking the query down into, say, two separate SELECTs, with six unions in each. Send the results to two temporary tables, then union the two tables into one final result.

Just a thought.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[TamarGranor]

Actually, prior to VFP 9, there was a limit on the number of unions in a query. I think it was 9.

Skylinc, the real problem here is that your database is badly designed. Having numbered columns like that is a sure sign that data's being repeated. It's much better design to use an additional table structured pretty much like the result you're looking for.

Tamar

 

[MrDataGuy]

I know the limit to the number of tables that could be joined was 9, so I assume that UNION had the same limit.

Lion Crest Software Services
Anthony L. Testi
President

http://www.LionCrestSoftwareServices.com

 

[Olaf Doschke]

In fact VFP has a Pivot table wizard in the Menu Tools->Wizards->Pivot Table.

Besides that the union approach is valid. As others already mentions the limitation dropped in VFP9, but you could do unions in two stages or simply use an appraoch of APPENDs, as in

Select cn_name, fcst1 as fcst, fdate1 as fdate from source into cursor curStart readwrite
Select cn_name, fcst2 as fcst, fdate2 as fdate from source into cursor curAppend

Select curStart
Append from DBF("curAppend")

Select cn_name, fcst3 as fcst, fdate3 as fdate from source into cursor curAppend

Select curStart
Append from DBF("curAppend")

...
Select curStart
Copy To destination.DBF Database Destionation.DBC

Bye, Olaf.

 

[Skylinc]

Thanks for the responses guys.

I will try MikeLewis's approach first (as it seems the simplest one), and if that doesn't work, then I will see if I can chat to the DB Admin to see if he could change the DB, although this seems unlikely.

Thank you!

 

[Olaf Doschke]

Mike Lewis is wrong, actually ERROR 1834 is "SQL: Too many UNIONs." and in previous VFP version it also could have been literally "too many unions" without the SQL: prefix.

Bye, Olaf.

 

[Skylinc]

The part of Mike's post that I will use is

"two separate SELECTs, with six unions in each. Send the results to two temporary tables, then union the two tables into one final result."

I already have the query split in two parts, and it does work, but merging the resulting CSV files into a single output is a time consuming process.

 

[Olaf Doschke]

Splitting unions was somethin I too recommended: "you could do unions in two stages"

Why are you merging two csv files? The split unions should each result in a VFP table or cursor and you can these with another union and only do the csv output with the final table.

Or are you not only exceeding the limitation on the number of unions but also the final table size? I actually doubt the last, as you actually already have all data in a DBF initially, splitting columns to records would just add few bytes, actually one per record, the deletion mark byte.

Bye, Olaf.

 

[Mike Lewis]

It's true that in VFP 8.0 and earlier the limit on the number of unions was nine, but that no longer applies.

Skylinc, what component are you using to access this table? If you're using the current OLE DB provider, that it compatible with VFP 9.0, and you shouldn't be subject to the limit.

But if you are using the old ODBC driver, that hasn't been updated since VFP 6.0, so the old limit will apply.

If that's the case, you should get the current OLE DB provider before you go any further. It might solve the problem.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Skylinc]

Just to let you all know, that the biggest cause of my problem was in fact that I was using the old ODBC driver (as mentioned by Mike, thanks). I have since installed and started using the OLD DB diver, and all is sorted now.

Thank you all for the help