?
Solved

Multiple Sum Columns per Row

Posted on 2013-01-25
4
Medium Priority
?
759 Views
Last Modified: 2013-01-30
I am working out of our sales table in DB and trying to put together a query that returns the following:

Customer    2010 Sales     2011 Sales     2012 Sales

Each customer may have any number of sales in a given year.  I have it set up currently that it provides three rows per customer, with a different years sales in each row (using SUM() and GROUP BY YEAR()) but this is more difficult for comparison.  

Is there any way to create a single query which returns aggregates in multiple columns per row?  

My current code:

SELECT
      Cu.CustomerName, Cu.SalespersonN,
            SUM(ART.SalesAnalysis)AS 'Sales',  YEAR(PostingDate) AS 'Year'
      FROM ARTransaction AS ART
      JOIN Customer AS Cu
      ON ART.CustomerN = Cu.CustomerN
      WHERE YEAR(PostingDate) >= '2010'
            AND YEAR(PostingDate) <= '2012'
            AND Cu.SalespersonN <> 0
                  
GROUP BY Cu.SalespersonN, Cu.CustomerName, YEAR(PostingDate)
ORDER By Cu.CustomerName, YEAR(PostingDate)
0
Comment
Question by:mjburgard
4 Comments
 
LVL 37

Assisted Solution

by:ValentinoV
ValentinoV earned 300 total points
ID: 38821980
You can do this using the PIVOT operator but it's not perfect.  The PIVOT operator expects you to hardcode the values of the field on which you're pivoting.  In your case that would be the year. Have a look at the examples through that link to get an understanding of how this works.

Hardcoding the year doesn't seem like the best way to go in this context. In order to avoid that, you could do the following.  First dump your data into a temporary table. Then use dynamic SQL to create the statement with the PIVOT operator, based on the values for the "Year" column in your temporary table.  It's a bit more work and no longer a single query, but it would work.  You could build a stored proc that does all this.

And to conclude, in case you happen to be using Reporting Services, you could simply use the Matrix control to pivot your data.  So if SSRS is an option, be sure to check it out!
0
 
LVL 35

Expert Comment

by:David Todd
ID: 38823456
Hi,

Something I do to get around this is label the columns year0, year1, ... where year0 is current, year1 is one year AGO, etc. This way the column labels never change as the data effectively scrolls across beneath them.
My pivot table resultsSo the 0, 1, 2 ... as column labels is fairly easy to generate
	select 
		st.SomeLabelOrSomething
		, datediff( day, st.SalesDate, getdate()) as DaysAgo
		, st.SalesTotalOrSoemthing
	from dbo.SomeTable st

Open in new window

I've generalised a fragment from the query that produced the results above - I'm interested in days, not years, but is good for minutes, hours, days, months, quarters, years. (Weeks is a little problematic with the beginning of the week and all that.)

To summarise - the nice dynamic labels are hard to code, and stuff that is hard to code, is hard to maintain, and hard to modify. I've coded something a little easier to read, and only need a small amount of effort in training my users etc in how to read the results.

HTH
  David
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 300 total points
ID: 38823608
Well, the first thing to consider is how are your users going to interact with the data.

If someting like Excel, or a report, or a screen, then those tools can often support a matrix or crosstab or tablix type function and can do all the hard work of transforming rows into columns.

Then you need to think about how to deliver the information.Does it have to be a "select" or can it be a Stored Procedure.

Because it is "year" then it isnt quite so bad to pivot, but still, the pivot function in SQL must explicitly know the new column names (as explicit literals) otherwise it wont pivot (some other DB's can derive that information and is very easy to pivot). Also being "year" you do have options to pivot (like a straight select case query).

To overcome having to know the columns, then strategies like dtodd works well "Current Year", "Last Year", "Prior Year" are nice generic columns and easy to cater for.

More importantly, they are then predictable column names so you can explicitly select "prior year" where as variable column names (like in a pivot) can create problems later when they change (though you do have a year to prepare, the difficulty is change of years and that transition from year to year).

So, first you need to ask yourself how are your users going to interact with the data, then you can work backwards from that to decide the best way to deliver the "raw" data.

But to answer your immediate request while you consider some of the above and previous postings, here is some code to play with :

-- the immediate need using PIVOT can be achieved with

SELECT *       
FROM  (SELECT Cu.Customername, Cu.SalespersonN, ART.salesanalysis, year(ART.postingdate) as yr
       FROM   ARTransaction AS ART
       JOIN   Customer AS Cu ON ART.CustomerN = Cu.CustomerN
       WHERE  YEAR(ART.PostingDate) >= '2010'
       AND    YEAR(ART.PostingDate) <= '2012'
       AND    Cu.SalespersonN <> 0 ) src
PIVOT
      (sum(salesanalysis) for yr in ([2010],[2011],[2012])) pvt


-- future proofing a little bit by extending the range of years, 
-- but then mucks up the select * (which isnt very good anyway)
-- note that you also have to change your YEAR selection as well 
-- which means as years progress, you are processing more and more data
-- unless you modify the query each time
 
SELECT *
FROM  (SELECT Cu.Customername, Cu.SalespersonN, ART.salesanalysis, year(ART.postingdate) as yr
       FROM   ARTransaction AS ART
       JOIN   Customer AS Cu ON ART.CustomerN = Cu.CustomerN
       WHERE  YEAR(ART.PostingDate) >= '2010'
--     AND    YEAR(ART.PostingDate) <= '2012'
       AND    Cu.SalespersonN <> 0 ) src
PIVOT
      (sum(salesanalysis) for yr in ([2010],[2011],[2012],[2013],[2014],[2015],[2016],[2017],[2018],[2019],[2020])) pvt


-- you can create the above as a view then simply select from the view and is a bit better
-- creating the view is a one time task and can then be used whenever you want, 
-- a view is essentially a virtual table over the real data, more like a stored query.

create view vw_pivot_sales as
SELECT *
FROM  (SELECT Cu.Customername, Cu.SalespersonN, ART.salesanalysis, year(ART.postingdate) as yr
       FROM   ARTransaction AS ART
       JOIN   Customer AS Cu ON ART.CustomerN = Cu.CustomerN
       WHERE  YEAR(ART.PostingDate) >= '2010'
--     AND    YEAR(ART.PostingDate) <= '2012'
       AND    Cu.SalespersonN <> 0 ) src
PIVOT
      (sum(salesanalysis) for yr in ([2010],[2011],[2012],[2013],[2014],[2015],[2016],[2017],[2018],[2019],[2020])) pvt
GO

-- selecting from the view makes it easier
select customername, salespersonn, [2010],[2011],[2012] 
from vw_pivot_sales

-- Now the problem with the above is the initial SELECT * and really you should select specific columns and maybe handle NULL values
-- to show you what I mean, lets play with that VIEW...
alter view vw_pivot_sales as
SELECT Customername,
       SalespersonN,
       isnull([2010],0) as [2010],
       isnull([2011],0) as [2011],
       isnull([2012],0) as [2012],
       isnull([2013],0) as [2013],
       isnull([2014],0) as [2014],
       isnull([2015],0) as [2015],
       isnull([2016],0) as [2016],
       isnull([2017],0) as [2017],
       isnull([2018],0) as [2018],
       isnull([2019],0) as [2019],
       isnull([2020],0) as [2020]
FROM  (SELECT Cu.Customername, Cu.SalespersonN, ART.salesanalysis, year(ART.postingdate) as yr
       FROM   ARTransaction AS ART
       JOIN   Customer AS Cu ON ART.CustomerN = Cu.CustomerN
       WHERE  YEAR(ART.PostingDate) >= '2010'
--     AND    YEAR(ART.PostingDate) <= '2012'
       AND    Cu.SalespersonN <> 0 ) src
PIVOT
      (sum(salesanalysis) for yr in ([2010],[2011],[2012],[2013],[2014],[2015],[2016],[2017],[2018],[2019],[2020])) pvt
GO

-- now selecting from the view avoids NULL and shows (for the user) a more accurate 0.00
select customername, salespersonn, [2010],[2011],[2012] 
from vw_pivot_sales

Open in new window


I have written a couple of Articles about PIVOT :
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/A_653-Dynamic-Pivot-Procedure-for-SQL-Server.html
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_4256-Dynamic-Pivot-Procedure-without-the-Pivot-function.html

And a mate has written one describing what dtodd was saying using generic columns :
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_654-Pivot-Rolling-Periods-Without-Using-Dynamic-T-SQL.html
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 800 total points
ID: 38827441
SELECT
      Cu.CustomerName, /*Cu.SalespersonN, --optional*/
      SUM(CASE WHEN PostingDate >= '20100101' AND PostingDate < '20110101' THEN ART.SalesAnalysis ELSE 0 END) AS [2010 Sales],
      SUM(CASE WHEN PostingDate >= '20110101' AND PostingDate < '20120101' THEN ART.SalesAnalysis ELSE 0 END) AS [2011 Sales],
      SUM(CASE WHEN PostingDate >= '20120101' AND PostingDate < '20130101' THEN ART.SalesAnalysis ELSE 0 END) AS [2012 Sales]
      FROM ARTransaction AS ART
      JOIN Customer AS Cu
      ON ART.CustomerN = Cu.CustomerN
      WHERE PostingDate >= '20100101' AND
            PostingDate < '20130101' --years 2010-2012
            AND Cu.SalespersonN <> 0
                 
GROUP BY Cu.CustomerName /*, Cu.SalespersonN*/
ORDER By Cu.CustomerName /*, Cu.SalespersonN*/


Btw, never perform a function on a column in a WHERE clause unless it is truly required.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

609 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