Solved

Pivot Table assistance in SQL Server 2005

Posted on 2013-01-09
12
230 Views
Last Modified: 2013-02-13
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
0
Comment
Question by:Mikeyman_01
  • 5
  • 4
  • 3
12 Comments
 
LVL 5

Expert Comment

by:RehanYousaf
ID: 38759707
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
 
LVL 12

Accepted Solution

by:
Jared_S earned 500 total points
ID: 38760563
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
 

Author Comment

by:Mikeyman_01
ID: 38760855
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
 
LVL 12

Expert Comment

by:Jared_S
ID: 38760870
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
 
LVL 5

Expert Comment

by:RehanYousaf
ID: 38761044
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
 

Author Comment

by:Mikeyman_01
ID: 38761437
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 5

Expert Comment

by:RehanYousaf
ID: 38762327
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
 

Author Comment

by:Mikeyman_01
ID: 38763314
I appreciate your time and energy on this Rehan...
ExcelPivot.jpg
0
 
LVL 5

Expert Comment

by:RehanYousaf
ID: 38764531
Btw when you run Jared's script ... are the results similar what you require ... if not not then what is missing?
0
 

Author Comment

by:Mikeyman_01
ID: 38765138
Havent tried it yet.. pending your response..
0
 
LVL 12

Expert Comment

by:Jared_S
ID: 38765748
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
 
LVL 5

Expert Comment

by:RehanYousaf
ID: 38766379
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

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now