At 06/25/2002 06:43 PM (Tuesday), PODESTA Mariano APRE wrote:

>look this example:
>
>i have a table with these data:
>
>Sales Representative |Seattle
>Vice President, Sales |Tacoma
>Sales Representative |Kirkland
>Sales Representative |Redmond
>Sales Manager |London
>Sales Representative |London
>Sales Representative |London
>Inside Sales Coordinator |Seattle
>Sales Representative |London
>
>
>how can i get this if the count of cities may change:
>
> |Kirkland |London |Redmond |Seattle |Tacoma
>Inside Sales Coordinator | | | |1 |
>Sales Manager | |1 | | |
>Sales Representative |1 |3 |1 | |
>Vice President, Sales | | | | |1

To me, the real question here is: Where is the result being used?

If an application is receiving the result it is really easy to have it
build the two-dimensional array which represents the results. Use a query
such as

Select "Title","Location",Count(*) as RecordCount
from "PivotTest"
group by "Title","Location"

which produces a result set like

"Inside Sales Coord","Seattle",1
"Sales Manager","London,1
"Sales Rep","Kirkland",1
"Sales Rep","London",3
"Sales Rep","Redmond",1
"VP Sales","Tacoma",1

Then loop through the result set. (I use Delphi for work such as this so I
would have two TStringList objects which hold the labels for each
dimension. If a label has not been seen before, add it to that dimension.
Otherwise, find the index of the label and record the count at the
intersection of the two indexes in a dynamic two-dimensional array of
integers.)

If you need this result in a report (using a reporting tool which only
knows SQL) then the biggest problem is how to match the row and column
labels with the counts. I don't know how reporting tools would do this
unless they had a built-in cross tabulation facility. However, if you had
to build it yourself, you have a natural soft limit on the columns which is
how many columns fit on a page. Using that limit I would write a stored
procedure which returns that many columns of counts. Assuming the title
list and the location list are going to be ordered alphabetically, the
stored procedure can return the counts for each title with columns matching
the alphabetic order of the locations. Then the hardest part left is, as I
mentioned above, getting the row and column labels to appear in the report
along with the counts.

How does everyone else do this cross tab stuff?