Showing counts of multiple values within a field horizontally 1

[Panama58]

We do merge purge work for direct mail campaigns. Our clients want to identify multi-buyers. Multi buyers are people who exist in more than one of the source-files that are being merged together. Our process creates a multis_m file which includes a sourcefile field and a buyer_cnt field. The buyer_cnt field will contain a value of '2' or higher depending on how many sourcefiles a particular individual was found in. Currently we run the following query:

sele sourcefile, buyer_cnt, cnt(sourcefile) from multis_m group by 1,2

This returns a query table that has three columns; the sourcefile, the value in buyer_cnt and the count of how many instances a particular value occurs within each sourcefile.

sourcefile buyer_cnt cnt
ACLU 2 563
ACLU 3 125
ACLU 4 25

What we want to do is create a table that we can copy to an excel spreadsheet with a seperate column for each unique value within the buyer_cnt field of the multis_m file. The new buyer_cnt fields would be populated with the count of how many times each value exists within the multis_m file.

sourcefile buyer_cnt2 buyer_cnt3 buyer_cnt4
ACLU 563 125 25

Maybe there is a nested SQL statement that will do this? Any help would be greatly appreciated.

Thanks, Panama58

 

[baltman]

SELECT SUM(IIF(some_condition = true, MyValue, 0))+00000 as MyValueSum

The '+00000' ensures the field will be wide enough. You may need to do a pre-query and then create the final SQL dynamically if your data will change.

You'd need a 'too many requested' column error catcher too.

Brian

 

[TamarGranor]

Use the cross-tab generator, VFPXTAB in the home directory. It'll give you exactly what you want.

Tamar