[AccessD] SQL Server Pivot

Mark,

You are correct that is my problem.  Additionally I need to be able to pull
all this out with the PKID because they want to do this using where clauses
on other fields in the table.  This method of storing the Age is just a
royal PITA.


John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark A Matte
Sent: Saturday, February 02, 2008 12:39 PM
To: Access Developers discussion and problem solving
Subject: Re: [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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com