Pivot Table assistance in SQL Server 2005

Hi Experts , I currently have my data as shown in the 'CurrentSQL'...I need it to look like the
'PivotInExcel'...

The other issue i am running into is that i need to replicate where the NULL fields are replicated based on 'Customer'...

This probably 2 different requests... but I am not smart enough to figure this out on my own

Thank you for your help
CurrentSQL.jpg
PivotInExcel.jpg
Michael KatzAsked:
Who is Participating?
 
Jared_SConnect With a Mentor Commented:
Are you looking for something like this?

select customer, customername, shippostalcode, shipaddress1, shipaddress2, shipaddress3, city, state, country
sum(case when masterbrand = 'Bestaste Shumai and Siopao' then 1, else 0 end) as [Bestaste Shumai and Siopao],
sum(case when masterbrand = 'Magnolia Deserts' then 1, else 0 end) as [Magnolia Deserts],
sum(case when masterbrand = 'Magnolia Meats' then 1, else 0 end) as [Magnolia Meats],
sum(case when masterbrand = 'Manila Gold' then 1, else 0 end) as [Manila Gold],
sum(case when masterbrand = 'Orientex' then 1, else 0 end) as [Orientex]

... repeat this case syntax for each variation in the master brand, the last case (prior to the "from" that you see below, will not be followed by a comma

from table
group by 
customer, customername, shippostalcode, shipaddress1, shipaddress2, shipaddress3, city, state, country

Open in new window

0
 
RehanYousafCommented:
can you please show desired result when there are null results

as in your sample result it does not show anything for null records
0
 
Michael KatzAuthor Commented:
Rehan, Jared_S

Thank you for your replies... very sorry for not being correct in my original requirement..

I think i made a mistake...I want 1 line per customer no nulls...as is the example i sent in my excel file...

Sorry if i mislead you both
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Jared_SCommented:
The query I posted should do something very very close to that.

When you have a customer with multiple ship-to addresses, then each location will have to be listed as a seperate line (creating multiple entries for a single customer).

Something to think about...
0
 
RehanYousafCommented:
Okay ... so far I understood is that you want 1 line per customer .... and sum of each brand in column rather than in row

Next question is ... what is the purpose of flag field in your table?

and also is it possible that you have two more addresses for same customer?
0
 
Michael KatzAuthor Commented:
The flag 0 implies a non sell and 1 implies a sell per Master Brand (ie..Magnolia Deserts, Orientex).. It is possible to have multiple Addresses per customer..Excellent Question...Wow i guess I really didnt explain this very well
0
 
RehanYousafCommented:
Now we getting some where :-) ... So if there are multiple addresses for the customer then what rules do you want to apply .... I mean, do you want to show two seperate rows or just select one address for customer and if one address than is there any priority or could be any?

I thick it will help a lot if you provide sample data covering all sort of scenarios and desired result in excel format.

Thanks
0
 
Michael KatzAuthor Commented:
I appreciate your time and energy on this Rehan...
ExcelPivot.jpg
0
 
RehanYousafCommented:
Btw when you run Jared's script ... are the results similar what you require ... if not not then what is missing?
0
 
Michael KatzAuthor Commented:
Havent tried it yet.. pending your response..
0
 
Jared_SCommented:
MikeyMan, I think you'll find that query does what you need (as far as I can tell) and should run fairly quickly.

You'll only need to create a case for each product/brand that you want to view in your pivoted data (which may cut down on some typing).
0
 
RehanYousafCommented:
I think Jared's query is query is probably quite closer to what you require ... only exception I can think of is probably master brands with null values will be seperate but hen you will have top provide the logic how to handle them

Only other problem i can see is that if your masters brand keeps getting updated ... in that scenario you will require dynamic query which will handle your master brands and we can work on that
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.