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
LVL 1
mjburgardAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Daniel WilsonConnect With a Mentor Commented:
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
 
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
 
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
All Courses

From novice to tech pro — start learning today.