Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 502
  • Last Modified:

SQL query to MS pivot table

The point of this query was to group up sales from a customer by year (2013,2012,2011,2010). After a lot of struggle I was able to get this to work in sql query almost the same as it was done before. The problem is that I cannot get it in a pivot table in excel, and the years 2013,2012, ect are listed as the rows when they should be column titles. I will attach a screen shot of what i have now, and what it needs to look like. I either need a way to import this as a pivot table with the years as the column titles, or a way to recreate this using MS query.
new-query.JPG
old-query.JPG
0
mjburgard
Asked:
mjburgard
  • 3
  • 2
1 Solution
 
Daniel WilsonCommented:
Would you please post the SQL for the new query so we have a starting point?

Thanks!
0
 
mjburgardAuthor Commented:
SELECT
      
      (Customer.CustomerName) as 'Customer',
      SUM(ARTransaction.SalesAnalysis) as 'Sales',
      YEAR(postingdate)as 'Year'
      
FROM
      ARTransaction, Customer
WHERE
      -- Change dates to desired timeframe
      PostingDate BETWEEN {d '2013-01-01'} AND {d '2013-12-31'}
      
      and salesAnalysis <> 0
      and ARTransaction.CustomerN = Customer.CustomerN
GROUP BY
    Customer.CustomerName,
    YEAR(postingdate)
      
ORDER BY
      SUM(ARTransaction.SalesAnalysis),
      Customer.CustomerName
0
 
Daniel WilsonCommented:
Ah, you're doing the PIVOT operation in Excel rather than in SQL, right?

I'll try to come up with a solution in SQL.  Excel's pivot options are powerful as far as they go, but do seem a little limited.
0
 
Daniel WilsonCommented:
OK, the TSQL PIVOT keyword isn't my greatest strength, but based on this documentation:
http://technet.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx

SELECT [Customer], [2010], [2011], [2012], [2013]

FROM (
SELECT
      
      (Customer.CustomerName) as 'Customer',
      SUM(ARTransaction.SalesAnalysis) as 'Sales',
      YEAR(postingdate)as 'Year'
      
FROM
      ARTransaction, Customer
WHERE
      -- Change dates to desired timeframe
      PostingDate BETWEEN {d '2013-01-01'} AND {d '2013-12-31'}
      
      and salesAnalysis <> 0
      and ARTransaction.CustomerN = Customer.CustomerN
GROUP BY
    Customer.CustomerName,
    YEAR(postingdate)
) X
PIVOT (
  Sum([Sales])
 For [Year] in ( [2010], [2011], [2012], [2013])
) as pvt 
Order by Sum([Sales], CustomerName

Open in new window

0
 
mjburgardAuthor Commented:
I was actually able to make some changes to the original finally and get it to work.. I did try your query up there and I was getting an error for compatibility error even when I used "set compatibility_level." Oh well, I got it to work a different way but thanks for your help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now