formatting results of the query

my current query returns data as follows:

stopID reason count

h1 e 5
h1 v 2
h1 a 1
h2 v 2
h2 m 8
h3 t 6
h3 v 1

Is it even possible to sort/format this query in SQL to
return the same data but in this type of format? Using
the values of the 'reason' column as column names?

stopID e v a m t

h1 5 2 1 0 0
h2 0 2 0 8 0
h3 0 1 0 0 6


thanks

-r

yes, but only if there is a finite and predetermined number of reasons

oh.

my.

$deity.


thanks, swampboogie, that's orgiastic

Thanks

I'm trying the crosstab procedure now with my query.

Thanks alot.

Hey thanks for the info. I ended up using pivot tables with the CASE statements.

One thing, I saw this question posted on the boards about the crosstab procedure. Im having the same problem. If anyone knows, let me know.



Thanks

try using MAX instead of SUM

Hi

Here is a line of my pivot table.

COUNT( CASE History.Reason WHEN 'MC' THEN Reason END ) AS MC.

When I use MAX, it doesnt count the reason totals.
When I use SUM I get this error: "The sum or average aggregate operation cannot take a varchar data type as an argument."

any idears?

thanks

dunno

would have to see the query

Okie, here it is.

SELECT
History.stopID, Route.Area1,
Route.AbcZone, Route.County, History.Pub,
COUNT( CASE History.Reason WHEN 'MC' THEN Reason END ) AS MC ,
COUNT( CASE History.Reason WHEN 'M' THEN Reason END ) AS M,
COUNT( CASE History.Reason WHEN 'N' THEN Reason END ) AS N ,
COUNT( CASE History.Reason WHEN 'NC' THEN Reason END ) AS NC
FROM
Route
LEFT JOIN
History ON Route.RouteId = History.stopID
RIGHT JOIN
History ON History.TransactionCntr = History.TransactionCntr

GROUP BY History.Reason,
History.stopID, CR_tblpRoute.Area1,
Route.AbcZone, Route.County, History.Pub

thanks

this part here needs to be fixed before anything else happens --
i personally have never needed both a LEFT and RIGHT in the same query

i'm not saying you don't need to here, but i doubt it

however, you have the History table in there twice, and do not qualify it properly

you can have the same table in a query twice, and it is usually written like this --
notice how the table aliases are used

thanks for pointing that out, r937. I fixed the left and right joins

Do you have any ideas what to do about the non numeric values in the crosstab resulting in seperate rows?



thanks

non-numerics?

it looks to me like they're supposed to be non-numerics --

COUNT( CASE History.Reason WHEN 'MC' THEN Reason END ) AS MC ,

you are counting non-numeric values, right?

and what did you mean by separate rows?

I guess non-numerics was'nt the right wording

When i do:

COUNT( CASE History.Reason WHEN 'MC' THEN Reason END ) AS MC ,
COUNT( CASE History.Reason WHEN 'M' THEN Reason END ) AS M