[dba-SQLServer] SQL Server Pivot

Hi John

I've done this a lot in Access.

1. Create a function that returns 1, 2, 3, etc. for any age as input.
2. Use this function in the query to retrieve the formatted column name: "Ctn" & CStr(GetAgeGroup(Age))

If the client wish to adjust the groups without entering the code, store age boundaries in a table with one column having a unique index and perhaps another column with the date from which the group is valid; this allows you to adjust the groups for a future date.

I guess you could create a single UDF to handle this.

/gustav

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com 
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Saturday, February 02, 2008 9:48 AM
To: 'Access Developers discussion and problem solving'; 'Discussion
concerning MS SQL Server'
Subject: [dba-SQLServer] 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