Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Pivot Table assistance in SQL Server 2005

Posted on 2013-01-09
12
Medium Priority
?
254 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 2000 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

927 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