Transpose data vertical to horizontal

[chaoma]

Hello, could someone please help me with this problem.

I have a data below:

Type Color Quantity
Cat Black 5
Cat Black 2
Dog Black 4
Cat White 3
Dog Red 2
Cat Blue 1
Fish Blue 11

Select Type, Color, count(*) as cnt, sum(Quantity) as sum
From Table
Group By Type, Color

Will give me result below:

Type Color cnt sum
----- ------ ------- ------
Cat Black 2 7
Cat White 1 3
Cat Blue 1 1
Dog Black 1 4
Dog Red 1 2
Fish Blue 1 11

However, I want my data to display in the format below:

Type Black Sum_blk Red Red_sum White sum_wht Blue sum_blue Purple sum_purple
Cat 2 7 0 0 1 3 1 1 0 0
Dog 1 4 1 2 0 0 0 0 0 0
Fish 0 0 0 0 0 0 1 11 0 0

Color could be add later and so is the type of animal. Is there an easier way to do this?

I was able to do this when there was only three color, but data keep growing and more color were added.

Thank you for any help.

Chaoma

 

[SQLDenis]

http://www.aspfaq.com/show.asp?id=2462

http://sqlservercode.blogspot.com/

 

[chaoma]

Thank you for your tip. I will try it now. Thanks again.

 

[chaoma]

Thank you again.

However, if the table was increase to include reptile, bird and other animals, and other colors as well. I would have to re-program to include those list as well. I want the query to do this automatcally if the list expand.

I looking into cusor( for each type and color)

DECLARE CURSOR TYPE FOR
SELECT TYPE FROM TABLE
OPEN TYPE
FETCH NEXT FROM TYPE INTO @TYPE
...
begin
DECLARE CURSOR COLOR FOR
SELECT COLOR FROM TABLE
OPEN TABLE
begin
......
end
....
end


Too complicate. The data is not in the same rows. Does anyone have a better solution. Any help is appreicated.