June 28, 2010 at 10:37 am
Hi,
I have the following table as temporary table output.
site customer ytd_sales
11099C000120 661637.44
11099C000121 2106488.85
11101C000120 661637.44
11101C000121 3310896.26
I need to get the output from manipulation of the above as follows:
cust_num 11099_sales_ytd 11101_sales_ytd 11099_sales_lastyr 11101_sales_lastyr total_sales_ytd total_sales_lastyr
c000120 661637.44 661637.44 200 150 1323274.88 350
c000121 2106488.85 3310896.26 300 650 5417385,11 950
I got the output without the last two columns using the following:
select cust_num, SUM(case site when '11099' then sales_ytd else 0 end) as [11099_sales_ytd]
, SUM(case site when '11101' then sales_ytd else 0 end) as [11101_sales_ytd]
,SUM(case site when '11099' then sales_lastyr else 0 end) as [11099_sales_lastyr]
,SUM(case site when '11101' then sales_lastyr else 0 end) as [11101_sales_lastyr]
from testcasel
group by cust_num
However I am not sure how to get the last two fields as shown above.
I appreciate any help for resolution of this issue. Thanks.
June 28, 2010 at 10:49 am
Your sample data and required output is quite hard to read.
Can you make it more readable?
Take a look at the article linked in my signature and find out how.
-- Gianluca Sartori
June 29, 2010 at 3:54 pm
Wouldn't it just be something like this?:
select cust_num,
SUM(case site when '11099' then sales_ytd else 0 end) as [11099_sales_ytd]
, SUM(case site when '11101' then sales_ytd else 0 end) as [11101_sales_ytd]
, SUM(case site when '11099' then sales_lastyr else 0 end) as [11099_sales_lastyr]
, SUM(case site when '11101' then sales_lastyr else 0 end) as [11101_sales_lastyr]
, SUM(sales_ytd) AS [total_sales_ytd],
, SUM(sales_lastyr) AS [total_sales_lastyr]
from testcasel
group by cust_num
Scott Pletcher, SQL Server MVP 2008-2010
June 29, 2010 at 5:48 pm
I saw your other post about a seemingly related query before ...
There is a fairly major problem in your question in that your select statement is pulling from a source that has not been defined. The temp table you refer to has different columns than you refer to in your select. And specifically, to differentiate between this year and last year there is going to need to be some type of date field, but you've not given any hint as to what that field looks like or where it is.
Besides this issue of a lack of information, there are other concerns. To be frank, I don't think you're going about this in a very good way. Rather than having a separate column for sales figures for each site it would tend to make much more sense to have a column for the site ID and a single set of columns for the sales figures related to that site.
Now maybe there is some specific reason why you're trying to get it to be a certain way, but without the structural definitions and sample data and an end result explanation of what you're trying to accomplish nobody is going to be able to give you very meaningful advice.
June 30, 2010 at 1:38 pm
Thanks everyone for the generous help. I am going to try to work on the example shown and figure out my output.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply