October 25, 2010 at 9:48 am
hi there,
I have the following tables
declare @targets table(id int,userid int,reportid int,periodid int,[target] int )
insert into @targets
select 1,450,1,226,10
union
select 2,450,1,227,8
union
select 3,450,1,228,7
union
select 4,450,1,229,4
union
select 5,450,1,230,5
union
select 6,450,1,231,6
union
select 7,450,1,232,2
union
select 8,450,1,233,0
union
select 9,450,1,234,1
union
select 10,450,1,235,11
union
select 11,450,1,236,6
union
select 12,450,1,237,1
declare @periods table (id int,contractyear int,period int, fromdate datetime,todate datetime)
insert into @periods
select 226,2010,1,'2010-08-01 00:00:00.00','2010-08-31 23:59:00.000'
union
select 227,2010,2,'2010-09-01 00:00:00.00','2010-09-30 23:59:00.000'
union
select 228,2010,3,'2010-10-01 00:00:00.00','2010-10-31 23:59:00.000'
union
select 229,2010,4,'2010-11-01 00:00:00.00','2010-11-30 23:59:00.000'
union
select 230,2010,5,'2010-12-01 00:00:00.00','2010-12-31 23:59:00.000'
union
select 231,2010,6,'2011-01-01 00:00:00.00','2010-01-31 23:59:00.000'
union
select 232,2010,7,'2011-02-01 00:00:00.00','2010-02-28 23:59:00.000'
union
select 233,2010,8,'2011-03-01 00:00:00.00','2010-03-31 23:59:00.000'
union
select 234,2010,9,'2011-04-01 00:00:00.00','2010-04-30 23:59:00.000'
union
select 235,2010,10,'2011-05-01 00:00:00.00','2010-05-31 23:59:00.000'
union
select 236,2010,11,'2011-06-01 00:00:00.00','2010-06-30 23:59:00.000'
union
select 237,2010,12,'2011-07-01 00:00:00.00','2010-07-31 23:59:00.000'
using the following statement
declare @year int = 2010,
@userid int = 450
select * from (
select
t.target,
p.period
from
@targets t
INNER JOIN @periods p on p.id = t.PeriodID and p.ContractYear = @year
WHERE
t.UserID = @userid
AND ReportID = 1
)x
pivot
(
max(target)
for period in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
)y
I get the following output
10874562011161
i wish to have an agregate column at the end that counts all the output up and creates the last column as column name 'Total'. my desired end product is as follows (bolded item is the new Total column, value = 61)
10874562011161 61
I wish to do it this way, rather than having the 'Total' as another row in teh database, so that i can guarantee the total will be the sum of the parts instead of relying on user input. Is this possible?
Thanks for reading.
October 25, 2010 at 12:37 pm
First of all: EXCELLENT job providing ready to use sample data, your current solution, current result and expected result. Nothing more to ask for!! THANK YOU!!
I stopped using PIVOT a while ago for several reasons, one the missing feature (or better the complexity) to add computed columns.
I went with (or better went back to) CrossTab instead (the method used prior MS introduced PIVOT). For details please see the related link in my signature.
And since you provided everything we need to return a coded version, herer's my approach:
;
WITH subqry AS
(
SELECT
t.target,
p.period AS periods
FROM
@targets t
INNER JOIN @periods p ON p.id = t.PeriodID AND p.ContractYear = @year
WHERE t.UserID = @userid AND ReportID = 1
)
SELECT
MAX(CASE WHEN periods =1 THEN target ELSE NULL END ) AS [1],
MAX(CASE WHEN periods =2 THEN target ELSE NULL END ) AS [2],
MAX(CASE WHEN periods =3 THEN target ELSE NULL END ) AS [3],
MAX(CASE WHEN periods =4 THEN target ELSE NULL END ) AS [4],
MAX(CASE WHEN periods =5 THEN target ELSE NULL END ) AS [5],
MAX(CASE WHEN periods =6 THEN target ELSE NULL END ) AS [6],
MAX(CASE WHEN periods =7 THEN target ELSE NULL END ) AS [7],
MAX(CASE WHEN periods =8 THEN target ELSE NULL END ) AS [8],
MAX(CASE WHEN periods =9 THEN target ELSE NULL END ) AS [9],
MAX(CASE WHEN periods =10 THEN target ELSE NULL END ) AS [10],
MAX(CASE WHEN periods =11 THEN target ELSE NULL END ) AS [11],
MAX(CASE WHEN periods =12 THEN target ELSE NULL END ) AS [12],
SUM(target) AS [total]
FROM subqry
October 26, 2010 at 2:21 am
thank you so much, Lutz.
I've used this way of bringing back information before but i think i'm constantly in the mindsite of using PIVOT so was trying to get it work that way!
cheers 😀
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply