Richard, A simple way to do this is to use a "Tally" table. A "Tally" table contains one column, ID, that contains numbers from 1 to [x]. You can then do joins against this table to do what you are looking for. Here's an example:
SELECT t.ID, a.Taken, a.assessment_ID FROM Tally as t LEFT OUTER JOIN assessment a on YEAR(a.Taken) = YEAR(DATEADD(YEAR,-(t.ID - 1),now())) WHERE t.ID <= 3 AND youth_id = 'X' That will output NULLs for years that don't exist. Let me know if you have any questions. Regards, Chris -----Original Message----- From: Richard Dillman [mailto:[EMAIL PROTECTED] Sent: Monday, September 17, 2007 8:13 PM To: CF-Talk Subject: Re: Pivot table confusion OK maybe I need to take a step back and give you some more detail. Or maybe I'm just not going to comprehend what you guys are telling me. I have a single table called assessment with these fields. [assessment_id] [int] IDENTITY(1,1) NOT NULL [youth_id] [varchar](26) NOT NULL [org_id] [varchar](26) NOT NULL [Taken] [datetime] NOT NULL [assessment_type] [int] NOT NULL Ive tried any number of answers from googling, and Ive been on this all day and I simply don't follow what your doing. But this is what I'm after. I'm only going to display 3 years so #Year(dateadd("yyyy",-2,now()))# Through #year(now())# but the part i get lost at is creating the empty cells for the values that Don't exist. -------------------2005--2006--2007--Total ------------------------------------------ assessment_type 1-----0-----5----10-----15 assessment_type 2-----3-----0----11-----14 assessment_type 3-----5-----5-----9-----19 ------------------------------------------ Grand Totals----------8----10----30-----48 I really appreciate all the help but you would think their was an easy way to do this with SQL. Seems it would be something you would need rather often. What I keep coming up with is the following table with the type repeated if theirs more than one year. -------------------2005--2006--2007--Total ------------------------------------------ assessment_type 1-----------5----10-----15 assessment_type 2-----3----------11-----14 assessment_type 3-----5------------------5 assessment_type 3-----------5------------5 assessment_type 3-----------------9------9 ------------------------------------------ Grand Totals----------8----10----30-----48 ----Totally Confused---- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Check out the new features and enhancements in the latest product release - download the "What's New PDF" now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:288714 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4