Help with group by query

[spiff2002]

Hi all
I am trying to group sales by month creating new columns by month.

Right now if I run my query like this

Select period,brand,division,sum(cases) as cases
From sales_table
group by period,brand,division
order by brand,period,division

and the result is

period brand division cases
2004-01 brand1 northern 200
2004-01 brand1 southern 356
2004-02 brand1 northern 345
2004-02 brand1 southern 334
2004-03 brand1 northern 234
2004-03 brand1 southern 543
2004-01 brand2 northern 456
2004-01 brand2 southern 213
2004-02 brand2 northern 890
2004-02 brand2 southern 98
2004-03 brand2 northern 345
2004-03 brand2 southern 789

I'm looking a result more like

brand division 2004-01 2004-02 2004-03
brand1 northern 200 345 234
brand1 southern 356 334 543
brand2 northern 456 890 345
brand2 southern 213 98 789

is this possible with a simple query??

Thanks in advance

 

[SQLBill]

You would probably have to create a temporary table, with the columns that you want, dump the information into that table and then query it from there.

-SQLBill

 

[spiff2002]

Thanks for the advice. I thought about a temporary table but I'm not sure how to create it.

 

[SQLBill]

Check out thread183-896831 for an example.

-SQLBill

 

[spiff2002]

SQLBill
I try something similar to this
Like you suggested

Create table #tempp (job int, subjob int)
insert into #tempp 
Select job, subjob from jcpmst group by job, subjob

After executing on Query Analizer, I guet this message

Server: Msg 2714, Level 16, State 6, Line 1
There is already an object named '#tempp' in the database.

I looked for the #tempp object on Enterprise manager but I don't see it
I even added this code at the begining of teh query

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[#tempp]') drop table [dbo].[#tempp]
GO

But I still get the same error.

We are running SQL server 2000 SP3

Thank you

 

[SQLBill]

Where did you 'look' for #tempp? Did you look in the TEMPDB database?

-SQLBill

 

[dbomrrsm]

a temp table stays in existence until the connection is dropped although you cant see it in EM. You can use a DROP TABLE #tempp to delete it - dont know why it wont work for you.

alternatively you could use

CREATE @MYTABLE TABLE (job int, subjob int)
INSERT INTO @MYTABLE
etc....

Then DROP TABLE @MYTABLE AT THE END

DBomrrsm

 

[spiff2002]

Guys
I appreciate the help on this and I don't want to deviate from the original question. I got the #tempp part working. I closed the connection and that deleted the temporary table.
But I still have to find a way to create columns using a group by field
In this case I want to create a column for each value on the field called period and that column will hold the number of cases for each month.
More like this

brand division 2004-01 2004-02 2004-03
brand1 northern 200 345 234
brand1 southern 356 334 543
brand2 northern 456 890 345
brand2 southern 213 98 789

 

[SQLBill]

Try running this and see if it works to return the proper SUM for 2004-01, brand1, northern.

SELECT 
   CASE WHEN Period = '2004-01'
          AND Brand = 'brand1'
          AND Division = 'northern'
        THEN (SELECT SUM(Cases) FROM tblname) AS '2004-01'
   END

If that returns 200, then we can work on putting the value into the temporary table.

-SQLBill