Group By problems - 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 2005 Forums

Transact-SQL (2005)

Group By problems

Author

Topic

portrman
Starting Member

24 Posts

Posted - 2009-12-24 : 17:56:10

I thought this would be a simply query but it's turning out much harder than I expected.

Here's my table:
AccountId varchar PK
Product varchar
Reseller varchar

Each account has 1 or more products they've purchased(no duplicate products for an account). For each product they've purchased, there is only 1 associated reseller. I'm trying to get a grid show each account, all the products and if they own the product, the reseller. So something like this.

-------Prod1----Prod2---Prod3
Acc1--------------r1------r2
Acc2----r1
Acc3----r1--------r3

It would be nice if the query can automatically tack in new columns as products are added, but I don't mind if I have to hardcode that aspect, only like 8 products now and we won't add many more over the years.

Thanks for the help!

portrman
Starting Member

24 Posts

Posted - 2009-12-24 : 18:03:36

Ok, so I was trying to be to complex in my earlier tries and I've now figured out something that works, but it seems terribly in-efficient, does anyone have a better solution?

SELECT
DISTINCT apr.AccountId
, (SELECT Resellers FROM AccountProductResellers WHERE AccountId = apr.AccountId AND Product='Prod1') Prod1
, (SELECT Resellers FROM AccountProductResellers WHERE AccountId = apr.AccountId AND Product='Prod2') Prod2
, (SELECT Resellers FROM AccountProductResellers WHERE AccountId = apr.AccountId AND Product='Prod3') Prod3
, (SELECT Resellers FROM AccountProductResellers WHERE AccountId = apr.AccountId AND Product='Prod4') Prod4
FROM AccountProductResellers apr

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2009-12-26 : 07:23:44

It is just a standard pivoting data. You should use PIVOT (non standard table operator).

SELECT
DISTINCT AccountId
, CASE WHEN Product='Prod1' THEN Reselleres ELSE '' END AS Prod1
, CASE WHEN Product='Prod2' THEN Reselleres ELSE '' END AS Prod2
, CASE WHEN Product='Prod3' THEN Reselleres ELSE '' END AS Prod3
, CASE WHEN Product='Prod4' THEN Reselleres ELSE '' END AS Prod4
FROM AccountProductResellers 
GROUP BY AccountId

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-28 : 00:53:04

If the values are dynamic,use
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

portrman
Starting Member

24 Posts

Posted - 2010-01-04 : 18:48:56

quote:

Originally posted by ms65g

It is just a standard pivoting data. You should use PIVOT (non standard table operator).

SELECT
DISTINCT AccountId
, CASE WHEN Product='Prod1' THEN Reselleres ELSE '' END AS Prod1
, CASE WHEN Product='Prod2' THEN Reselleres ELSE '' END AS Prod2
, CASE WHEN Product='Prod3' THEN Reselleres ELSE '' END AS Prod3
, CASE WHEN Product='Prod4' THEN Reselleres ELSE '' END AS Prod4
FROM AccountProductResellers 
GROUP BY AccountId

This generates errors stating that AccountProductResellers.Product and AccountProductResellers.Resellers can't invalid in the select list because they aren't in an aggregate which is what I was getting from the beginning. But then I remembered the subselects would but are just nasty looking.

I'll take a look at the PIVOT and the Dynamic PIVOT code .. thanks madhivanan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-10 : 07:51:59

quote:

Originally posted by ms65g

It is just a standard pivoting data. You should use PIVOT (non standard table operator).

SELECT
DISTINCT AccountId
, CASE WHEN Product='Prod1' THEN Reselleres ELSE '' END AS Prod1
, CASE WHEN Product='Prod2' THEN Reselleres ELSE '' END AS Prod2
, CASE WHEN Product='Prod3' THEN Reselleres ELSE '' END AS Prod3
, CASE WHEN Product='Prod4' THEN Reselleres ELSE '' END AS Prod4
FROM AccountProductResellers 
GROUP BY AccountId

You may need to use MAX or MIN function

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-10 : 07:52:37

<<
I'll take a look at the PIVOT and the Dynamic PIVOT code .. thanks madhivanan
>>

You are welcome

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources