Help with Group By - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with Group By

Author

Topic

b.nicovski
Starting Member

2 Posts

Posted - 2012-01-21 : 09:47:15

Hello to all,

Here is my problem. I'm using this query for following table:

SELECT dbo.Movies.Title, dbo.Movies.Director, dbo.Movies.ReleaseYear, dbo.Categories.CategoryName
FROM dbo.Categories INNER JOIN
dbo.MoviesInCategories ON dbo.Categories.Id = dbo.MoviesInCategories.CategoryId INNER JOIN
dbo.Movies ON dbo.MoviesInCategories.MovieId = dbo.Movies.Id
GROUP BY dbo.Movies.Title, dbo.Movies.Director, dbo.Movies.ReleaseYear, dbo.Categories.CategoryName

MOVIES
Avatar James Cameron 2010-01-01 Action
Avatar James Cameron 2010-01-01 Drama
Ice Age 2 Chris Wedge 2011-05-01 Comedy
Ice Age 2 Chris Wedge 2011-05-01 Drama

I want the result to look like this:

Avatar James Cameron 2010-01-01 Action Drama
Ice Age 2 Chris Wedge 2011-05-01 Comedy Drama

Categories to be in same row?

Please help me, i need quick answer.

Thank you

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-21 : 10:24:45

Here is a way to do this - changes to your original code are in red; I have not created a table with test data or tested it,so....

;WITH cte AS
(
   SELECT dbo.Movies.Title,
         dbo.Movies.Director,
         dbo.Movies.ReleaseYear,
         dbo.Categories.CategoryName,
         ROW_NUMBER() OVER (PARTITION BY dbo.Movies.Title,dbo.Movies.Director,dbo.Movies.ReleaseYear
            ORDER BY dbo.Categories.CategoryName) AS RN
   FROM   dbo.Categories
         INNER JOIN dbo.MoviesInCategories
            ON  dbo.Categories.Id = dbo.MoviesInCategories.CategoryId
         INNER JOIN dbo.Movies
            ON  dbo.MoviesInCategories.MovieId = dbo.Movies.Id
   GROUP BY
         dbo.Movies.Title,
         dbo.Movies.Director,
         dbo.Movies.ReleaseYear,
         dbo.Categories.CategoryName    
)
SELECT
   Title,
   Director,
   ReleaseYear,
   MAX(CASE WHEN RN=1 THEN CategoryName END) AS Category1
   ,MAX(CASE WHEN RN=2 THEN CategoryName END) AS Category2
   -- ,MAX(CASE WHEN RN=3 THEN CategoryName END) AS Category3
   -- and more categories if you have many
FROM
   CTE
GROUP BY
   Title,
   Director,
   ReleaseYear;

b.nicovski
Starting Member

2 Posts

Posted - 2012-01-21 : 10:44:10

Thanks a lot, u r my savior :)

I find this on net

create table mytable (id int identity(1,1), PersonID int, Unit varchar(10))
insert into mytable values (1,'Che')
insert into mytable values (1,'Mat')
insert into mytable values (1,'Phy')
insert into mytable values (2,'Che2')
insert into mytable values (2,'Mat2')
insert into mytable values (2,'Phy2')
insert into mytable values (3,'Phy3')

SELECT t1.PersonID,
Units =REPLACE( (SELECT Unit AS [data()]
FROM mytable t2
WHERE t2.PersonID = t1.PersonID
ORDER BY Unit
FOR XML PATH('')
), ' ', ',')
FROM mytable t1
GROUP BY PersonID ;

drop table mytable

But i can't integrate with my code. Maybe u will know how to do that? Because i think this is more flexible solution.

Thank you again

quote:

Originally posted by sunitabeck

Here is a way to do this - changes to your original code are in red; I have not created a table with test data or tested it,so....

;WITH cte AS
(
   SELECT dbo.Movies.Title,
         dbo.Movies.Director,
         dbo.Movies.ReleaseYear,
         dbo.Categories.CategoryName,
         ROW_NUMBER() OVER (PARTITION BY dbo.Movies.Title,dbo.Movies.Director,dbo.Movies.ReleaseYear
            ORDER BY dbo.Categories.CategoryName) AS RN
   FROM   dbo.Categories
         INNER JOIN dbo.MoviesInCategories
            ON  dbo.Categories.Id = dbo.MoviesInCategories.CategoryId
         INNER JOIN dbo.Movies
            ON  dbo.MoviesInCategories.MovieId = dbo.Movies.Id
   GROUP BY
         dbo.Movies.Title,
         dbo.Movies.Director,
         dbo.Movies.ReleaseYear,
         dbo.Categories.CategoryName    
)
SELECT
   Title,
   Director,
   ReleaseYear,
   MAX(CASE WHEN RN=1 THEN CategoryName END) AS Category1
   ,MAX(CASE WHEN RN=2 THEN CategoryName END) AS Category2
   -- ,MAX(CASE WHEN RN=3 THEN CategoryName END) AS Category3
   -- and more categories if you have many
FROM
   CTE
GROUP BY
   Title,
   Director,
   ReleaseYear;

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-21 : 12:27:55

The difference between what I posted and the XML PATH approach is that in what I posted, each category would be in a separate column. In the XML PATH approach, all the categories would be in one column, (each category separated by a separator of your choice, for example, separated by comma's). From your original posting, I got the impression that what you are looking for is data in separate columns - in which case, the XML PATH approach would not work.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources