query help

[rotelmak]

I have table with this records

product_id quantity color
1 10 red
1 20 black
1 30 green
1 40 red
1 50 black
2 60 black
2 70 green
2 80 black

I want with query to got this result

product_id red black green
1 50 70 30
2 0 140 70

There are only 3 colors (red, black, green)

 

[AlexCuse]

This should do the trick:

[COLOR=blue]select[/color] product_id
, sum([COLOR=blue]case[/color] [COLOR=blue]when[/color] color = [COLOR=red]'red'[/color] [COLOR=blue]then[/color] quantity [COLOR=blue]else[/color] 0 [COLOR=blue]end[/color]) [COLOR=blue]as[/color] red
, sum([COLOR=blue]case[/color] [COLOR=blue]when[/color] color = [COLOR=red]'black'[/color] [COLOR=blue]then[/color] quantity [COLOR=blue]else[/color] 0 [COLOR=blue]end[/color]) [COLOR=blue]as[/color] black
, sum([COLOR=blue]case[/color] [COLOR=blue]when[/color] color = [COLOR=red]'green'[/color] [COLOR=blue]then[/color] quantity [COLOR=blue]else[/color] 0 [COLOR=blue]end[/color]) [COLOR=blue]as[/color] green
[COLOR=blue]from[/color] leTable
[COLOR=blue]group[/color] [COLOR=blue]by[/color] product_id

Basically you use the CASE statement to filter your sums by what color they are. Make sense?

HOpe it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[rotelmak]

Works fine.
Thank you Alex

 

[AlexCuse]

Glad it helped

Ignorance of certain subjects is a great part of wisdom