Solved

Pivot Table assistance in SQL Server 2005

Posted on 2013-01-09
12
237 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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
 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

821 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