[AccessD] SQL Server Pivot

John,

If I understand what your asking...this isn't quite a normal PIVOT situation.  A PIVOT uses data(the values) as row headers and column headers.  Your example sounds like you want to use data as column headers and column names as row headers?

If I had to do this is access I would use iif statements to determine what field the data came from.

Good luck,

Mark A. Matte

************************SQL*********************
TRANSFORM Count(*) AS Expr1
SELECT IIf(IsNull([55_64])=True,IIf(IsNull([65_74])=True,"45_54","65_74"),"55_64") AS FieldName
FROM tblJC
GROUP BY IIf(IsNull([55_64])=True,IIf(IsNull([65_74])=True,"45_54","65_74"),"55_64")
PIVOT IIf(IsNull([55_64])=True,IIf(IsNull([65_74])=True,[45_54],[65_74]),[55_64]);
************************SQL*********************


> From: jwcolby at colbyconsulting.com
> To: accessd at databaseadvisors.com; dba-sqlserver at databaseadvisors.com
> Date: Sat, 2 Feb 2008 11:47:53 -0500
> Subject: [AccessD] SQL Server Pivot
>
> I have a bunch of columns that break out ages into bands. For example:
>
> Presence_of_adults_age_65_74_specific
> Presence_of_adults_age_55_64_specific
> Presence_of_adults_age_45_54_specific
> etc
>
> Each column has codes 1,2,3
>
> I need counts of each column, for each value
>
> column Cnt1 Cnt2 Cnt3
> 65_74 45 3 23
> 55_64 103 48 211
> 45_54 20 1 49
>
> etc
>
> I think the Pivot statement is going to do that for me but I can't seem to
> wrap my mind around the syntax. Can anyone point me to a web page that
> clearly explains this or simply write a SQL statement that does this?
>
> Thanks
>
> John W. Colby
> Colby Consulting
> www.ColbyConsulting.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com

_________________________________________________________________
Climb to the top of the charts! Play the word scramble challenge with star power.
http://club.live.com/star_shuffle.aspx?icid=starshuffle_wlmailtextlink_jan