Help with a pivot Query

Could someone please help with the following:

     I need to create a query that will pull from one table 
'Order_Line_Invoice' and display in the following output.

                      2009-2010                2008-2009         Differances
                      -------------                ------------          
--------------
CustID         Units  Sales               Units   Sales       Units   Sales
  1                   5       $20                   3          $25         2 
         $5
  2                   1       $15                   2          $20         1 
         $5


Would anyone know how to do this type of thing.  If so could you please 
write a sample query that I could use to learn from and manipulate.  I just 
need a starting point for doing this type of thing.


Thanks
-- 
Dave
0
Utf
9/10/2010 8:48:06 PM
sqlserver.programming 1873 articles. 0 followers. Follow

2 Replies
1328 Views

Similar Articles

[PageSpeed] 9

Sorry that output example became skewed, it should look like this:  Note the 
09-10 & 08-09 unit and sales have to be between those date ranges.

            09-10           98-09       diff
            -------           -------       ----
cust    unit sales    unit sales  unit sales
1          5     $10      3    $15     2     $5
etc.

  
-- 
Dave


"David" wrote:

> Could someone please help with the following:
> 
>      I need to create a query that will pull from one table 
> 'Order_Line_Invoice' and display in the following output.
> 
>                       2009-2010                2008-2009         Differances
>                       -------------                ------------          
> --------------
> CustID         Units  Sales               Units   Sales       Units   Sales
>   1                   5       $20                   3          $25         2 
>          $5
>   2                   1       $15                   2          $20         1 
>          $5
> 
> 
> Would anyone know how to do this type of thing.  If so could you please 
> write a sample query that I could use to learn from and manipulate.  I just 
> need a starting point for doing this type of thing.
> 
> 
> Thanks
> -- 
> Dave
0
Utf
9/10/2010 8:57:03 PM
David (duckkiller53@gmail.com) writes:
> Could someone please help with the following:
> 
>      I need to create a query that will pull from one table 
> 'Order_Line_Invoice' and display in the following output.
> 
>                       2009-2010                2008-2009         
>Differances
>                       -------------                ------------          
> --------------
> CustID         Units  Sales               Units   Sales       Units   
>Sales
>   1                   5       $20                   3          $25         
>2 
>          $5
>   2                   1       $15                   2          $20         
>1 
>          $5
> 
> 
> Would anyone know how to do this type of thing.  If so could you please 
> write a sample query that I could use to learn from and manipulate.  I
> just need a starting point for doing this type of thing. 
 
Here is a query that runs in the Northwind databaes and which displays
the number of orders handled by each employee each year:

SELECT E.LastName,
       [1996] = SUM(CASE Year(OrderDate) WHEN '1996' THEN 1 ELSE 0 END),
       [1997] = SUM(CASE Year(OrderDate) WHEN '1997' THEN 1 ELSE 0 END),
       [1998] = SUM(CASE Year(OrderDate) WHEN '1998' THEN 1 ELSE 0 END)
FROM   Orders O
JOIN   Employees E ON O.EmployeeID = E.EmployeeID
GROUP  BY E.LastName



-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
9/10/2010 9:04:40 PM
Reply:

Similar Artilces:

Help with Expressions
We inventory wood poles in 19' lengths which we then cut into smaller pieces for our customers. Is there an expression I can use to help me reduce waste when we are cutting the poles? At one time, we may be cutting 10 to 30 peices in differing lengths ranging from 18" to 160" or larger. On Sun, 22 Nov 2009 05:42:01 -0800, LeLe <LeLe@discussions.microsoft.com> wrote: >We inventory wood poles in 19' lengths which we then cut into smaller pieces >for our customers. Is there an expression I can use to help me reduce waste >when we are cutting the p...

Function Help Please
Tuesday, April 14, 2009 That data above, once imported from a tab delimited file, is interpreted by Excel as General format. This is a couple of years worth of this data. I'd like to shorten the date while removing the day from the beginning. Highlighting the cell then manually changing the format to Date does nothing. I think my only option is to use a function that keeps all data after the first space. Thanks, AJ Select the column with those "dates" in it, then click Edit/Replace on Excel's menu bar... type these four characters (without ...

An Object could not be found??!? Please Help!
I have seen this question asked a few times on Web Forums as well as newsgroups, however it seems that none of the solutions seem to work for me. Here is my scenario. I recently purchased a brand new Dell Server and workstations. The Dell Server is configured as a PDC running Windows Server 2003. The workstations are running Windows XP with Office 2003 installed. The only other software installed on both server and workstations are Symantec Corporate Edition Virus v.7.6, and Exchange Server 2003 on the server. I ran the Exchange Server setup and all seemed to go well. I created a mailbo...

Using data from cells in a Query to a MS Access Database
I need to use a MS Excel 2003 spreadsheet to perform a simple set of calcs. One piece of information I need is stored in an Access Database. If I could have, I would have converted the spreadsheet to an MS Access database and programmed the query to use information from text boxes. However, not all employees in the company have MS Access on their computer. So I need to use an external query in Excel to look at the MS Access Database. I can do that pretty easily. The Query Wizard walks you right through it. However, I need to make it so that the criteria for the query is drawn ...

vlookup problem please help
Hi experts, I have a problem in using vlookup function and have not been able to resolve for a year+. Hope I can find some help in this great forum. Many thanks in advance! An example as below. When I vlookup Material2 to Material1 for the Project No, I get the result 222-56 for all the duplicate Material1 values. In this case of having duplicate values in Material1 but associated with different Project Numbers, how can I have vlookup function to match the Project No. containg "AA" as 1st priority ? (and if there is no Project No. containing "AA", then vlookup to look f...

Inventory reconciliation gone wrong.. help???
Hi, Have a real serious problem.... was doing a inventory reconciliation when great plains crashed.. not whenever you try to get into any transaction entry window the error, 'cannot post while inventory quantities are being altered' and we cannot enter any documents.. Is there any way we can resume the inventory reconciliation or stop it?? Cheers ...

book query
I would like a recommendation for a good book on Excel. I would place myself as an intermediate who has just started to learn some formulae. I use Excel XP, 97 and also Excel X on my mac I would like it to be not too heavy going so as not to put me off learning Any help would be appreciated Bobby For information and examples on Excel's functions, you can download Peter Noneley's Function Dictionary: http://homepage.ntlworld.com/noneley/ or Norman Harker's information on functions: http://www.contextures.com/functions.html and John Walkenbach's book on formul...

Help with a template
I had a Quotation template that I was using to help my husband with his small business. I cannot find the template and when I try to open it up on my system to see other quotes or estimates I have done for him, it is a blank screen. Help, please. Tell me what I am doing wrong or how I can redownload another template. Thanks for any help. I don't know what you are doing wrong. Maybe you could download another template using the method you used to download the last one. Templates are not difficult to create (File>Save As>Template); you could create your own. -- Greeting from the Gulf...

Forms Help
I have a form, the form has an ID that goes through many test/parameters. All the test/parameters associated with the ID remains on a sub form for viewing. I would like to develop a series (a-z) for each ID,were repeating information for the id is carried throughout all ID's and the one test paramter that differed, thus changing the Series Letter will be changed. Do you have any suggestions. Thanks LA -- Message posted via http://www.accessmonster.com On Wed, 22 Aug 2007 14:04:05 GMT, "misschanda via AccessMonster.com" <u36612@uwe> wrote: >I have a form, the for...

Help Please #8
Thanks For The Info : -- mikeee ----------------------------------------------------------------------- mikeeee's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1461 View this thread: http://www.excelforum.com/showthread.php?threadid=26424 ...

Creating a Pivot Chart
I am sure there is a pretty simple trick to this that I just cannot figure out. I would like to put these currencies into a pivot table and be able to filter by Country. My ideal situation would be able to set up a Pivot graph that I could pick and choose which currency I wanted to show using the report filter. Everytime I try I cannot get this result. My sheet is set up as the following. Colums have the Country Currency Ticker USD, JPY, GBP, etc with the values beneath them. The rows are labeled by date starting at 1/1/2006 to 10/22/08 starting in cell. The column labels start ...

Help with hierarchy in organization charts
I am trying to create a basic org chart for my organization. I am having difficulty representing people at different levels that report to the same person. For example, a manager and a director might report to a partner. I want the manager to appear lower on the chart than the director, but want a direct line to the partner above both people. I realize I can manually lower the manager, but when I use the re-layout function the manager is elevated back to the level of the director. Any ideas? This was easy to accomplish in OrgPlus and I assume there must be a way in Visio as well. Thanks!...

item barcodes and sql query
This is a multi-part message in MIME format. ------=_NextPart_000_0007_01C89274.4D47EC80 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hello, Customer forgot to set the right barcodes for the items they left it blank. Customer would like to print labels but since they did not specify the right barcode types in the item properties I guess it will not work. So is there a sql query that can do that? For example I would like to it to use upca for all items, since almost all of their products are upca's. Thank you. ------=_NextPart_000_0007_01C...

cannot use wildcards in LDAP query
I am using genifax and two of the tools they provide error out when using wildcards as part of an LDAP query in the production envioronment. I also tried the tool against servers running LDAP in different sites but they fail. I tested this tool against a lab machine all the same service pack/patches and it works correctly. Any ideas? sorry this is a 5.5 server running sp4 on nt4 sp6a "Charles Hibbits" <Charles_hibbits@hotmail.com> wrote in message news:%23v7mrVmJEHA.4052@TK2MSFTNGP11.phx.gbl... > I am using genifax and two of the tools they provide error out when using...

Need help with Access report
I designed an access report with a facility and a buch of contacts for that. I created a group on the facility ID. I want only a certain type of contact to show up in the facility group header and rest of the contact types in the details sectio. Before some takes a wag at answering this, could you provide a little better description? Individual contacts normally would display in the detail section, not "in the facility group header". Maybe you should type in some significant records/fields and how and why they should display in your report. -- Duane Hookom Microsoft Access M...

Help #7
I have Pub 2000 at work and Pub 2002/3 at home and I saved my file at home in 2000 format and brought it to work to print on 11x17 and I have lost my formatting related to tab settings and it now tells me that this is a web page and tabs don't work. I did not save it as a web page can I somehow convert it back to a Pub format so my tabs work? It is an 8 page program. Don't know why it would turn into a web page... Did you copy the publication to the local hard drive before opening it? This article may give you insight into the tab issue. Custom tabs that you previously set in Publ...

Pivot Chart Filter
I have two Pivot charts that compare similar data and have them displayed side-by-side. When I change the filters in one chart, I want to apply the same filter for the other chart. Is there a way to have one Pivot Chart Filter Pane control two or more Pivot Charts without having to click each chart and change to filter for each one? ...

Help needed for a simple spreadsheet
I use MS office 2000, using excell to calculate costings, i would lik to simplyfy the way i use the spreadsheet to calculate costs but canno find out how to set up a basic database which will automatically link t the spreadsheet, any help would be gratefully appreciated. Using a simple 5 column spreadsheet i would like to set it up a follows. Column A - enter simple item code (upto 10 digits) which wil auto-complete after 3 digits and allow scrolling through similar code until the correct item is found, column C would then automatically b entered with the full item description and column D...

Editor help.
when I reply to an email message I get a pop up that tells me that word is either busy or can't be found and that it will open instead with Outlook editor. I've turned on the word editor in my tools and options mail format tab but no luck. I've noticed also that the checked box to use word as editor does not stay checked. I've done all updates and still the problem persists. Any help? ...

Keep conditional format when "show pages" from Pivot table
When I use "show pages" to drill down the data into pages from Pivot table, I find the conditional format on pivot table is missing. I have to make conditional format again to every page. How to keep the conditional formatting when I use "show pages" from pivot table? Maybe you could just apply Data|filter|autofilter to your original data range and filter to show what you need to see. Angus wrote: > > When I use "show pages" to drill down the data into pages from Pivot table, I > find the conditional format on pivot table is missing. I have to m...

Data Connection: Access Query to Excel How to get rid of header ro
Hello. I have created a data connection in Excel to an Access query. It does exactly what I need however the header rows of the query are imported as well and all I want are the data in the simplest way possible. Thanks in advance. It depends on how you setup the data connection. If you're doing it in VBA using ADO and a reference to the query, the default is to not import the field headings. If you're doing it without VBA, using Get External Data on the Data ribbon in Excel 2007 you'll get the headings. Here's a short example using Micros...

FIREWALL SETUP HELP
will some one tell me if I am using Terminal Server (ts) or Fast User Switching Session (fus) on my new HP. I am unable to download a new firewall program . the message reads: the firewall does not support remote TS, nor FUS Session. if using TS log on directly not remotely if using FUS disable it thus allowing firewall to work HELP ASAP!!! On Fri, 14 Mar 2008 17:38:34 -0700, "Mary Ann" <jam9991@aol.com> wrote: Out of scope of this newsgroup, which is about Microsoft Access database program. -Tom. >will some one tell me if I am using Terminal Server (ts) or Fas...

Excel self replication
Hi I have a mojor problem, a 14 Mb workbook with about 10 sheets inside, with various lookups, validation, etc but NO MACROS. When I try and delete or insert a column, Excel opens a copy of the workbook and another Excel sheet (blank). The whole computer locks up and Excel effectively crashes by 'Not responding'. Any advice greatly appreciated. Thanks in advance . . . . Stuart Maybe you have macros where you didn't look. Debra Dalgleish has nice instructions for getting rid of macros at: http://www.contextures.com/xlfaqMac.html#NoMacros Maybe it'll help you find...

Help!!!! #2
Step 1: I have range of numbers lets say 50, 25, 30 and so on.... Step 2: I want to find 10% of of the range in above ex 5, 2.5, 3... Step 3: The numbers to be rounded of to 5,3,3....... The following will help for first 3 steps i. =Round(CountA("Range")*10%),0) Step 4: Now the tricky situation a) Specific to the range as in above ex for range with 50 numbers the result shoulbe the Average of the highest 5 numbers, Lowest 5 numbers, Average of the range; b) for range with 25 numbers the result shoulbe the Average of the highest 3 numbers, Lowest 3 numbers, Average of th...

Help on tracking email
Hi, When recieved emails with track token from outside, I always got another email with the subject : FW:.... I could not find a rule to ask it to forward to my local account. So what is wrong with it? Any ideas? Thanks, Cindy You can Forward emails/activities from within CRM. It may be possible someone is forwarding these to you. "Cindy" wrote: > Hi, When recieved emails with track token from outside, I always got another > email with the subject : FW:.... I could not find a rule to ask it to forward > to my local account. So what is wrong with it? > Any ideas...