[RESOLVED] Help displaying query properly

[STEVESKI07]

I am creating a temp table dynamically with some data and I need help in creating a query to return the data how I'd like. Here is an example of how the data looks:

Months Type Item
February 1 12345
March 3 65467
January 2 46132
December 3 87946
February 2 16561
January 1 46543


I want to retrieve the count of the items ordered based on month and type. This is how I'd like to have the data displayed:

Months Type1Count Type2Count Type3Count
January ---- 1 ------------ 1 ------------ 0
February --- 1 ------------ 1 ------------ 0
March ----- 0 ------------ 0 ------------ 1


Hopefully this makes sense. Any help would be greatly appreciated

[Charles]

You write that you're generating this table dynamically. Is there a way that you can genreate it a little differently? Something like:

Months Type1 Type2 Type3
January True Null Null

[STEVESKI07]

You write that you're generating this table dynamically. Is there a way that you can genreate it a little differently? Something like:

Months Type1 Type2 Type3
January True Null Null

That's essentially what I'm trying to create now, but I don't know if it's possible. I can't really do much with how I'm creating the initial temp table with the way it is set up. I'm doing a few calculations before I calculate the type, so that is the result after the type is calculated.

I know you can't do this, but this is basically what I'm trying to do:

Select Months, Count(Select count(*) from table where type=1), Count(Select count(*) from table where type=2), Count(Select count(*) from table where type=3)

[Charles]

Here's something that works in Oracle:select * from (
select "Months" , count (*) as "Type 1"
from TABLE_NAME
where type= 1
group by "Months"
) join (
select "Months" , count (*) as "Type 2"
from TABLE_NAME
where type= 2
group by "Months"
) using ("Months" )
join (
select "Months", count (*) as "Type 3"
from TABLE_NAME
where type= 3
group by "Months"
) using ("Months" )

[STEVESKI07]

Here's something that works in Oracle:select * from (
select "Months" , count (*) as "Type 1"
from TABLE_NAME
where type= 1
group by "Months"
) join (
select "Months" , count (*) as "Type 2"
from TABLE_NAME
where type= 2
group by "Months"
) using ("Months" )
join (
select "Months", count (*) as "Type 3"
from TABLE_NAME
where type= 3
group by "Months"
) using ("Months" )


Yes, that would be exactly what I'm looking for. Does anybody know the SQL Server equivalent to this?

[Charles]

The only difference will be in the joins. According to O'Reilly's SQL Pocket Guide SQL Server doesn't understand the "using". You'll have to join "on".

[STEVESKI07]

You're right. I had to restructure it a little bit to get the joins to work, but it is working now. Thanks!