[dba-SQLServer] Grouping values in crosstab

jwcolby jwcolby at colbyconsulting.com
Fri Nov 16 07:30:19 CST 2007


You might remember I asked if it is possible to group values in a field
directly in a crosstab.

For example a crosstab of length of boat by state where you group 7-15 the
count of the values (lengths) in a crosstab column called 7 to 15.  Well...
I tried it directly in the crosstab.

SELECT St as USState, [7,8,9,10,11,12,13,14] as '7 To 14'
FROM(
SELECT     IR3863.dbo.vBaseBoatLength.St,
IR3863.dbo.vBaseBoatLength.Boat_Length_4_through_50_feet
FROM       IR3863.dbo.vBaseBoatLength ) tmpBoatLength
PIVOT 
(
COUNT(tmpBoatLength.Boat_Length_4_through_50_feet)
FOR tmpBoatLength.Boat_Length_4_through_50_feet IN([7,8,9,10,11,12,13,14]))
AS tmpBL
ORDER BY USState

vBaseBoatLength pulls the ST and Boat_Length_4_through_50_feet fields.

The query did not complain and is currently running.  I will report back
when I get the counts, which I will then cross check against a simple
count() where IN() query, but it certainly looks like it might be working.

John W. Colby
Colby Consulting
www.ColbyConsulting.com 




More information about the dba-SQLServer mailing list