Two dimenstions 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

SQL Server 2005 Forums

Transact-SQL (2005)

Two dimenstions GROUP BY

Author

Topic

sqlquestion2
Starting Member

2 Posts

Posted - 2011-04-07 : 11:27:32

Hi,

I have a table (this is a short example)

Amount Category Date
123 Food 1/1/2011
234 Food 1/1/2011
345 Food 1/2/2011
333 Food 1/2/2011
111 Energy 1/1/2011
222 Energy 1/1/2011
333 Energy 1/2/2011

I would like getting the following table as a result:
Food Energy
1/1/2011 357 333
1/2/2011 678 333
Average 517.5 333
Total 1035 666

Hope it displays good on the browser, the general idea is having to groups, one to be displayed on the columns and one on the rows. So, I'll have the dates on the left (rows) and Categories on the top (columns), in the middle I'll get the SUM of the values which belong to both of the Date and the Category. If nothing exist so i expect getting a 0 ammount.

How can I do such a thing. Using a duplicate GROUP BY yields lots of rows, which I don't want.

Thanks,

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-07 : 11:48:16

If your categories are known in advance, you could use the pivot operator - as in this example:

create table #tmp (Amount int, Category varchar(255), Dt datetime);

insert into #tmp values ('123','Food','1/1/2011');
insert into #tmp values ('234','Food','1/1/2011');
insert into #tmp values ('345','Food','1/2/2011');
insert into #tmp values ('333','Food','1/2/2011');
insert into #tmp values ('111','Energy','1/1/2011');
insert into #tmp values ('222','Energy','1/1/2011');
insert into #tmp values ('333','Energy','1/2/2011');

select
   *
from
   #tmp
PIVOT
   ( sum(Amount) for Category in ([Food],[Energy]))p

drop table #tmp;

If you don't know the categories in advance, you would need to use dynamic pivoting. See here for example: http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-07 : 11:52:01

Missed the part about sum and average in my initial response.

Many experienced people on this forum might suggest that it is better done either in a separate query (so you get two record sets) or to do it in the presentation layer (if that is the destination for the results of the query). Nonetheless, if you really really wanted to, you could do something like this:


with cte as
(
   select
      *
   from
      #tmp
   PIVOT
      ( sum(Amount) for Category in ([Food],[Energy]))p
)
select * from cte

union all

select
   null,
   avg(Food),
   avg(Energy)
from
   cte
   
union all

select
   null,
   sum(Food),
   sum(Energy)
from
   cte

I have not considered the ordering of the rows here, but that is easily added if you need to.

sqlquestion2
Starting Member

2 Posts

Posted - 2011-04-07 : 12:54:40

Hoo yeah, I need the dynamic one. I have, both, in the Categories and Dates more than two, and they're unknown.

Now, I'm using it under ASP.NET, using C#. I'm connecting it to a DataGrid.
Is it possible, at all, using C# with all of this code?
How?

Brrrr. This looks scary...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-07 : 13:11:09

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources