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

Similar Articles

[PageSpeed] 14

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 on templates
I don't expect a complete answer, but if anyone can tell me a document to read - or whatever - about creating templates. I'm still a novice. What happens to me is that I've named ranges and tables on Sheet1. When I create a number of additional sheets corresponding to months, the formulas on these sheets reference Sheet1 and not the sheets upon which they reside. How could I create formulas that will reference the sheets upon which they are entered? And is there a way to simply insert this customized sheet whenever I should need it? Again, I'm willing to purchase a b...

stock or grants? ... help !
does anyone can explain to me, in a very easy way, what are the "stock option grants" ? Do they deal with the grants given away to non-profit organizations? Thanks In microsoft.public.money, vinnie wrote: >does anyone can explain to me, in a very easy way, what are the "stock >option grants" ? Those are employee stock options. > >Do they deal with the grants given away to non-profit organizations? No. ...

CANNOT SEND and RECIEVE EMAIL PLEASE HELP ASAP
Hi, I am on the process of re-doing our network Currently we have 2 servers running Windows NT 4.0 the first server is acting as the File Server and that's where the users are authenticating as well and the socond one is our exchange server. Our Exchange Server is working before and since I installed a new Windows 2003 Server to replace our old file server we cannot send emails at all and we are recieving emails that is at least 4 hours delayed. I didn't installed the Exchange Server originally and I really don't have experience with it so I am wondering if maybe someon...

Dotnet newbie needs some help with XML
I need to be search an XML document to find a specific attributes value. Here is a sample of the XML document: <?xml version="1.0" encoding="utf-8" ?> <localization> <language type="English"> <translation forid="lblEmail" text="Email:"/> <translation forid="lblPassword" text="Password:"/> <translation forid="lblregistration" text="If you are a new user and don't have an account"/> </language> <language type="German">...

MS Query returning incomplete results
Hi, Have been using Excel Queries for a while with no problems. Since last week, queries are coming back incomplete, returning blanks for cells that should have data. Have imported the data into Access and the queries work properly. Any suggestions ? Thanks, Mike ...

How do I make Microsoft Query a menu item?
Trying to create a drop-down menu selection for Microsoft Query in Excel without first having to go through the Wizard. ...

help on vbProper case Curtis's idea
Hi Curtis, recently I was looking for a way to change the text in a textbox to proper case. I finally decided to use your idea, because I think it is a very clever way to approach this question. But I would like to ask you one more question: Is it possible to enhance this code so that the user can even chose between upper and lower case? I mean, if you press tab the text change to proper, as it do now, but if you press "shift + tab" it becomes upper case, for example! using another key combination, alt + tab, it change again in lower case... I am not good with VBA code and I don...

Check box and make table query
I am trying to write a very basic make table query pulling some of the data from a form. the form has a check box, [MO] and a default value of -1 (this is to defaul all records as checked) If I run a datasheet veiw of the query, the table populates with the "-1" when I run the make table the field shows ?? and a data type of bianary. The query is pulling from the form as MOR:[forms]![Frm_RunNew]![MO] If I uncheck the box it show zero on view and null when run. any suggestions? I added Cbool to my query and all is fine now.. thanks to anyone who may have taken t...

No data return from a sub-query causes an error message
I have a sub-query which counts the number of referrals received [Date Received]. The user then enters the criteria of the time period between [start date] and [end date] and the locality [Town]. There are 9 different localities. When the query is run and there have been no referrals in a particular locality, the table appears as column headings with no data. The query that this links into (as do several other sub-queries) then also returns no data. How can I get the first sub-query to return data for each of the different localities, with 0 if there have been no referrals? If someone ca...

need help
WE HAVE GREAT PLAINS, AND NOW HAVE BUSINESS PORTAL, AND WE NEED HELP DISIGNING AND USING IT. CAN ANYONE HELP ME? Norma, Thanks for using the newsgroup. I would recommend placing a call with MBS Support at 888-477-7877. Do you have some specific questions I can help you with from the newsgroup? I would be happy to help you out with some basics of Business Portal. Thanks Jake "NORMA JEAN" wrote: > WE HAVE GREAT PLAINS, AND NOW HAVE BUSINESS PORTAL, AND WE NEED HELP > DISIGNING AND USING IT. CAN ANYONE HELP ME? I think that is my main problem. I know a little abo...

Help me kill an infuriating formatting glitch
I am trying to remove an infuriating formatting glitch from a table I am copying from Excel to Word. In Excel it looks like a normal table. There is no sign of any funny formatting. But when copied to Word the top left cell appears as a "cell within a cell". Instead of the cell having a solid blue background, it appears as a blue box within a larger white cell. I've tried everything to get rid of the "cell within a cell". I've merged and unmerged the cell. I've used the format painter to put a correct cell onto it. If I try to tab out of that cell it ins...

Pivot Table Problem #4
I have a workbook that contains 10 separate sheets. Each sheet has data that is arranged in 3 columns with the same column labels and same type of data. I want to create a pivot table that will consolidate all 10 of the data sets. I am able to do this using the multiple range functionality but it does not produce the results I am looking for. I have tried changing the layout multiple ways to no avail. It will if I do one pivot table for each but then I would need to merge all of the individual Pivot Tables which I can not seem to do. What I want is this: Columns a,b,c of each sheet hav...

pivot table label adding "2" to the end of a label name
Hi, I have a pivot table and sometimes it adds a "2" to the end of a row lable ie. "joes pizza" is displayed as "joes pizza2". the sorce data certanly doesn't include the "2" and it seems like it is trying to tell me some thing ;). It might be hapening after i do a "replace command" in the source data (replace "sams pizza" with joes Pizza"). any one experienced this? Cheers, The only time I've seen this is when I had multiple headers with the same name. If I were you, I'd check once more--don't forget to c...

Please Help with Formula!
Hello, I am a new user and I am having great difficulty in figuring out something. I have a dataset in Excel with first names, last names, states, cd sales total cost by state... and I am I started a new worksheet cales total sales y state. Now, in this new sheet I want have the states copied over and in the B2 cell I want to write a formula that will enable me to have the total cost from O2:O112 cells ("Total Sales") to be sumed up by each state with absolute references. How would I do this? Example I am currently using =SUMIF(Demographics!F2:F112, "AK", Demographics!O2:O...

Simple query question in MS Access
I am using MS Access to create this database and making a query that will show my score of the winning team and the name of the winning team. This is what my tables currently look like in Access: [img]http://i50.tinypic.com/scf1av.jpg[/img] To create the winning score I want to use: max(homeScore, awayScore) And to show the winning team I want to use: IIf(homeScore>awayScore, [homeTeam.name], [awayTeam.name]) Where do I put these expressions in the query? I tried to put them in the field and Criteria but this is not working for some reason. I must be doing something ...

use VBA to run query duplicate and and increment if number already in table
ok I have been looking through all treads but have not found one that could help me. If i missed the tread sry for this post.. Anyways I have a form that would ask a user to enter an id numer. The i want the field to run a query to see if it that number is already in the table.... if it is in the table i want it to increment the last number by one. so ID: BMO-151-TD05-1 if match Then ID: BMO-151-TD05-2 *this number can't be in the system either* pleaseeeee help me!!! Thanks mike -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms...

VBA and form/query help sought
I have a form based on a query, with the text box txtEmailAddress displaying the field EmailAddress from the source query. This textbox (along with others) is then used to create an email in Outlook using: Private Sub cmdOpenOutlook_Click() On Error GoTo Error_Handler Dim objOutlook As Outlook.MailItem Set objOutlook = CreateObject("Outlook.Application") Set objEmail = objOutlook.CreateItem(olMailItem) With objEmail .BodyFormat = olFormatRichText .To = Me.txtEmailAddress .CC = Me.txtCCEmailAddress .Subject = Met.txtEmailSubject .HTML = txt.MessageText ...

Help to bind a new form on an account
Hi, I would like to create a new aspx page in order to add the management of the expenses to MSCRM. To do this, I need to recover the user id in this page, I tested with the BizUser WhoAmI method but that does not work. Also I would like to bind the account note for example in the contact form. Does somebody could help me? Are you using the BizUser class right? I am using this=20 class to retrieve the authenticated user and works fines. Try it: BizUser usuario =3D new BizUser(); usuario.Credentials =3D=20 System.Net.CredentialCache.DefaultCredentials; //diretorioVirtual =3D 'http:...

Pivoting Text Data
Is it possible to pivot text data in Excel? I have a spreadsheet with 3 columns of text data. Moving from left t right (i.e. Column A to column C), the data is broken down into mor granular detail. For example: A. Column A contains the host name of a PC. The host name is repeate in each row that applies to that host. B. Column B are the titles of the data. i.e. Memory, HD Size, # o processors, etc. The titles repeat as necessary for each of the hos names in column A C. Column C is the data itself. It is text, not numeric I'm trying to pivot the data such that: 1. Each host name (fr...

Double Axix Charting off a pivot table
I have a chart pointing to a pivot table as its source. Is there any way for me to add a secondary axis with the pivot table being the source data? Thanks! George 1. Right-click on the series that you want to plot on the secondary axis. 2. Choose 'Format Data Series' 3. On the Axis tab, select 'Secondary Axis' 4. Click OK Note: If you refresh the chart, the formatting will be lost. You can record a macro as you apply the formatting, then run the macro after a refresh, to reapply the formatting. george wrote: > I have a chart pointing to a pivot table as its s...

Help File in Exchange 2003 SP2
I support several customers who utilize Exchange 2003 with SP2. Whenever I try to use the "Help" function in Exchange Systems Manager, it causes System Manager to crash. Does anyone know a cause/fix? In news:EFF04356-B603-48D3-8A3C-399F8B543628@microsoft.com, DonL <DonL@discussions.microsoft.com> typed: > I support several customers who utilize Exchange 2003 with SP2. > Whenever I try to use the "Help" function in Exchange Systems > Manager, it causes System Manager to crash. Does anyone know a > cause/fix? Any error messages? What do you see in your e...

Help with Bill Download and Account Reconcile (M2004)
Not sure if this was discussed before. New to this group. This is Money 2004 deluxe and with Bank America account. I set up all my bill pay at the website, not from Money. (Not sure if this makes any difference.) When Money downloads the online bill pays, the bills are entered immediately and the account debited. Sometimes it is up to 30 days in advance. For instance, if my account has $2,000 balance and I have a $3,000 bill 25 days away, I still see a $1,000 negative balance when the bill is downloaded. Called the bank and they said they can not turn off the bill download. Any solutions? ...

Pivot Tables...
I'm looking to learn Pivot Tables. Can anyone out there let me know any good texts on this? Thank you in advance. Pivot Table Data Crunching by Jelen & Alexander; quepublishing ISBN 0-1897-3435-4 web tutorials at: http://www.cpearson.com/excel/pivots.htm http://peltiertech.com/Excel/Pivots/pivotstart.htm http://www.contextures.com/xlPivot02.html http://www.ozgrid.com/Excel/excel-pivot-tables.htm http://www.techonthenet.com/excel/pivottbls/index.htm http://www.dicks-blog.com/archives/2005/06/23/download-pivottable-parameters/ best wishes -- Bernard V Liengme Microsoft Excel MVP w...

column query
Greetings I am trying to return a value in a column of a table based on a value in a cell in the same row but different column. For example, column A is a list of names, column B is a list of scores. In a separate area i want to display the highest score with the name of the person that achieved it. I've tried using MAX to display the highest score but cannot think of a way to show who this value relates to in column A. Does this make sense? Many thanks for any help Assuming: col A = Names, col B = scores data from row2 down and there's *no ties* for the maximum score If you h...

help...Send/receive error (0x800CCC6D)
ISP is Road Runner and I have Yahoo Web Hosting and related email. I Use POP and SMTP to my Outlook so I can read emails through Outlook. I have not had any problems until recently. My Send/receive is constantly showing the following error, regardless of how big the file being sent is and the send/receive process is now taking forever. I've tried rebooting, called my ISP who said they can't help, and reset my existing account.....all to to effect. The error is as follows: Task'sp2@group - sending and receiving' reported error (0x800CCC6D):'your outgoing (SMT...