I have a table of over 21,000 rows. The data is a cross-reference that
allows a person to find what type of transmission they have in their
car, depending on what Make, Model, Year, Engine and Option (option is
a boolean) they have. The existing table has an entry for each
possible combination. I'm attempting to reduce the number of rows by
specifying a year range instead of a row for each year.

He is a sample set of data (the orginal table):

Make	Model	Year	Engine	Option	Trans	TG
ACURA	CL 	1997	L4 2.2	N	A6VA	540
ACURA	CL 	1998	L4 2.3	N	B6VA	540
ACURA	CL 	1997	V6 3	N	M7ZA	540
ACURA	CL 	1999	V6 3	N	M7ZA	540
ACURA	CL 	2000	V6 3	N	M7ZA	540
ACURA	CL 	2001	V6 3	N	M7ZA	540
ACURA	INTEGRA 1997	L4 1.8	N	MP7A	540
ACURA	INTEGRA 1997	L4 1.8	N	S4XA	540
ACURA	INTEGRA 1997	L4 1.8	N	SP7A	540

Here's what I need the outputted table to look like:
Make 	Model	YrStart	YrEnd	Engine	Option	Trans	TG
ACURA	CL 	1997	1997	L4 2.2	N	A6VA	540
ACURA	CL 	1998	1998	L4 2.3	N	B6VA	540
ACURA	CL 	1997	1997	V6 3	N	M7ZA	540
ACURA	CL 	1999	2001	V6 3	N	M7ZA	540
ACURA	INTEGRA 1997	1997	L4 1.8	N	MP7A	540
ACURA	INTEGRA 1997	1997	L4 1.8	N	S4XA	540
ACURA	INTEGRA 1997	1997	L4 1.8	N	SP7A	540

The platform is MS SQL server 7.0, however a SQL 2K box is available
if needed. This is a one time query; graceful, speedy code isn't
needed... Go crazy with cursors if that helps. :-)

Hello,

Would it be an option for you to export all the data into a tab
delimited format, or something of the sort?  If so, I imagine it
wouldn't be too difficult a task in perl.  After the data is exported,
you would rename the table (in case things don't go well), and then
re-import from the output of the perl script.  If this is a helpful
solution, let me know.

What is the criterian to group the records in Year range.
I can see you have reduced the following 4 rows to 2 rows but if all
the rows have same information then why can't we reduce all these rows
to 1 row.
ACURA CL  1997 V6 3 N M7ZA 540 
ACURA CL  1999 V6 3 N M7ZA 540 
ACURA CL  2000 V6 3 N M7ZA 540 
ACURA CL  2001 V6 3 N M7ZA 540 

Please clarify how to reduce rows on what basis?
Thanks,
-Mangesh

Try something along the lines of this SQL statement:

================================

SELECT Make, Model, MAX(year) AS maxyear, MIN(year) AS minyear,
engine, option, trans, TG
FROM test
GROUP BY Make, Model, engine, option, trans, TG

================================

This should group all cars together by make, model, engine, option,
trans, and TG, with a calculated max and min values for the year. 
Note that this assumes that the car is available for EVERY YEAR
between the start and end year, regardless of whether or not this is
true in the data.  (That is, if a particular car has entries for 1986,
1987, and 2001, the min will be 1986 and the max will be 2001.)


Hope this answers your question,
Sundiata

Declare @Make nvarchar(10),
	@Model nvarchar(10),
	@Year nvarchar(10),
	@Engine nvarchar(10),
	@Option nvarchar(10),
	@Trans nvarchar(10),
	@TG nvarchar(10),
	@Count nvarchar(10), 
	@YrStart nvarchar(10),
	@YrEnd nvarchar(10)



DECLARE Data CURSOR FOR
	SELECT Make, Model, [Year], Engine, [Option], Trans, TG FROM old 
OPEN Data
FETCH NEXT FROM Data
INTO @Make, @Model, @Year, @Engine, @Option, @Trans, @TG

WHILE @@FETCH_STATUS = 0
BEGIN
	SELECT @Count = COUNT(*) from new where Make = @Make and Model =
@Model and Engine = @Engine and [Option] = @Option and Trans = @Trans
and TG = @TG

	IF @Count >= '1' 
	BEGIN 
		SELECT @YrStart = YrStart, @YrEnd = YrEnd FROM new where Make =
@Make and Model = @Model and Engine = @Engine and [Option] = @Option
and Trans = @Trans and TG = @TG

		IF @Year > @YrStart 
			SET @YrEnd = @Year

		IF @Year < @YrStart 
			SET @YrStart = @Year


		UPDATE new SET YrStart = @YrStart, YrEnd = @YrEnd where Make = @Make
and Model = @Model and Engine = @Engine and [Option] = @Option and TG
= @TG

	-- END If @Count >= '1'
	END

	IF @Count = '0' 
		INSERT INTO new (Make, Model, YrStart, YrEnd, Engine, [Option],
Trans, TG) VALUES
			(@Make, @Model, @Year, @Year, @Engine, @Option, @Trans, @TG)
	-- END IF @Count = '0'

	FETCH NEXT FROM Data
	INTO @Make, @Model, @Year, @Engine, @Option, @Trans, @TG

-- END WHILE
END

close data
DEALLOCATE data

select * from new

Accidently pressed the 'Post Comment' buttom b4 I was ready...

I think that this will do what you are looking for... you may
want/need to play with the data types...

if you need any more info, let me know... 

:^)