Solved

Pivot Table assistance in SQL Server 2005

Posted on 2013-01-09
12
235 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

914 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

16 Experts available now in Live!

Get 1:1 Help Now