seperating the ''group by'' output.
-
if you have thousands of records that you've obtained from
a query using the 'group by' method, how could you then seperate
the output in sets.
for example; if you had say hundreds of movie titles, and all were
grouped by release date... i would like the results of the query
to dispay each set of dates seperated from each other.
this possible?
_________________________
-
I think it is possible to group by more than one item
-
yeah, but i'm talking more about taking the output that was
already grouped via the select statement, but breaking up
that into different sets.
does that make sense?
if it is possible... how?
_________________________
-
Do the select into a temp table and then select / group on that.
-
if i understand you correctly you are saying that once
a temp table is populated with these values... i can
again run a 'select/group by' statement and the results
will be in seperated sets?
i don't believe that would change any thing other than
creating a temp object with the same results.
is that right?
_________________________
-
right... ok no problem.
i can make piece with alot of temp tables.
problem is... there are so many different sets within
the results i'll have to some how manually script in
the different temp objects, unless there is a way to create
temp tables based on distint results.
i dunno... this is all way beyond me. i'll see what i can do.
thanks for all the help!
_________________________
-
check this out... of course this is exactly what i'm looking
for, and these guys want to charge for the answer.
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21825243.html
_________________________
-
Do you mean multiple result sets, or just a blank line or two between the groups when you run the query in QA?
-
multiple result sets is what i'm trying for.
got an idea?
_________________________
-
Your answer lies in the phrase 'result sets'. A result set is the dataset returned as the result of a DML statement. To get multiple result sets, you must run multiple DML statements. You will need to run a SELECT...GROUP BY statement for each specific result set you want to get back.
-
Here's a simple example that uses a cursor (sorry!
).IF EXISTS(SELECT name FROM sysobjects WHERE name = N'movies' AND type = 'U')
DROP TABLE movies
GO
CREATE TABLE movies
(
mid int IDENTITY(1,1) PRIMARY KEY
, title varchar(50)
, releaseYear int
)
GO
SET NOCOUNT ON
INSERT movies (title, releaseYear) VALUES ('The Lost City' , '2005')
INSERT movies (title, releaseYear) VALUES ('Broken Flowers' , '2005')
INSERT movies (title, releaseYear) VALUES ('Coffee and Cigarettes' , '2003')
INSERT movies (title, releaseYear) VALUES ('Lost in Translation' , '2003')
INSERT movies (title, releaseYear) VALUES ('The Royal Tenenbaums' , '2001')
INSERT movies (title, releaseYear) VALUES ('Osmosis Jones' , '2001')
INSERT movies (title, releaseYear) VALUES ('Speaking of Sex' , '2001')
INSERT movies (title, releaseYear) VALUES ('Charlie''s Angels' , '2000')
INSERT movies (title, releaseYear) VALUES ('Hamlet' , '2000')
INSERT movies (title, releaseYear) VALUES ('Scout''s Honor' , '1999')
INSERT movies (title, releaseYear) VALUES ('Cradle Will Rock' , '1999')
INSERT movies (title, releaseYear) VALUES ('Rushmore' , '1998')
INSERT movies (title, releaseYear) VALUES ('With Friends Like These...', '1998')
INSERT movies (title, releaseYear) VALUES ('Wild Things' , '1998')
INSERT movies (title, releaseYear) VALUES ('The Man Who Knew Too Little' , '1997')
INSERT movies (title, releaseYear) VALUES ('Larger Than Life' , '1996')
INSERT movies (title, releaseYear) VALUES ('Kingpin' , '1996')
INSERT movies (title, releaseYear) VALUES ('Ed Wood' , '1994')
INSERT movies (title, releaseYear) VALUES ('Mad Dog and Glory' , '1993')
INSERT movies (title, releaseYear) VALUES ('Groundhog Day' , '1993')
INSERT movies (title, releaseYear) VALUES ('What About Bob?' , '1991')
INSERT movies (title, releaseYear) VALUES ('Quick Change' , '1990')
INSERT movies (title, releaseYear) VALUES ('Ghostbusters II' , '1989')
INSERT movies (title, releaseYear) VALUES ('Scrooged' , '1988')
INSERT movies (title, releaseYear) VALUES ('Little Shop of Horrors' , '1986')
INSERT movies (title, releaseYear) VALUES ('The Razor''s Edge' , '1984')
INSERT movies (title, releaseYear) VALUES ('Nothing Lasts Forever' , '1984')
INSERT movies (title, releaseYear) VALUES ('Ghost Busters' , '1984')
INSERT movies (title, releaseYear) VALUES ('Tootsie' , '1982')
INSERT movies (title, releaseYear) VALUES ('Stripes' , '1981')
INSERT movies (title, releaseYear) VALUES ('Loose Shoes' , '1980')
INSERT movies (title, releaseYear) VALUES ('Caddyshack' , '1980')
GO
DECLARE @year int
SELECT DISTINCT releaseYear INTO #years FROM movies
DECLARE cYears CURSOR FOR SELECT * FROM #years ORDER BY releaseYear
OPEN cYears
FETCH cYears INTO @year
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT title, releaseYear
FROM movies
WHERE releaseYear = @year
ORDER BY title
FETCH cYears INTO @year
END --WHILE
CLOSE cYears
DEALLOCATE cYears
SET NOCOUNT OFF
DROP TABLE #years
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply