Simple... but how? - 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 2000 Forums

Transact-SQL (2000)

Simple... but how?

Author

Topic

paulodeleo
Starting Member

2 Posts

Posted - 2002-02-25 : 15:57:07

Hi,

I have a table like this:

product_name product_color product_total
------------ ------------- -------------
prod1 Blue 1
prod1 Red 2
prod2 Blue 2
prod2 Red 4
prod3 Blue 5

How do i get the same data, but in that format:

product_name blue_products red_products total
------------ ------------- ------------ -----
prod1 1 2 3
prod2 2 4 6
prod3 5 0 5

Thanks in advance,
Paul.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-25 : 16:09:23

Use the CASE statement to create a cross-tab:

SELECT product_name,
Sum(CASE product_color WHEN 'Blue' THEN product_total ELSE 0 END) Blue_Products,
Sum(CASE product_color WHEN 'Red' THEN product_total ELSE 0 END) Red_Products,
Sum(product_total) Total
FROM product_table
GROUP BY product_name

You can add extra CASE statements for additional colors. Search SQL Team for "cross tab" or "pivot table" and you'll get several articles on cross-tab methods.

jbkayne
Posting Yak Master

100 Posts

Posted - 2002-02-25 : 16:18:03

I like Rob's solution better, but you could also do it this way:

select
distinct product_name
, isnull((select product_total from Products where product_color = 'blue' and product_name=prod.product_name),0) as blue_products
, isnull((select product_total from Products where product_color = 'red' and product_name=prod.product_name),0) as red_products
, isnull((select product_total from Products where product_color = 'blue' and product_name=prod.product_name),0) + isnull((select product_total from Products where product_color = 'red' and product_name=prod.product_name),0) as total
from Products as Prod

paulodeleo
Starting Member

2 Posts

Posted - 2002-02-25 : 16:28:42

Thanks very, very much guys!

jvolpe
Starting Member

2 Posts

Posted - 2005-09-01 : 11:42:53

One other question. Let's say you want to pivot this info...

Name Email
Jason V jason.volpe@x.com
Jason V jason.volpe@google.com
Jason V jason.volpe@msn.com

How do you get the email addresses into Email1 Email2 Email3, etc columns without knowing the previous email for each column. I'm wanting to do this in a view.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources