June 29, 2008 at 11:44 pm
Hi
My issue is I want to create a matrix like structure i.e
if i have 6 months & its revenue like
month revenue
1 10
2 15
3 20
4 12
5 16
6 20
now i want the structure like
month revenue 1 2 3 4 5 6
1 10 25 45 57 73 93
2 15 35 47 63 83
3 20 32 48 58
4 12 28 48
5 16 26
6 20
here the logic behind this calculation is..
under column heading '1' for 1st month
revenue of 1st month + revenue of 2nd month = 10 +15=25
for 2nd monht under column 1st
revenue of 2nd month+ revenue of 3rd month = 15+20 = 35
at 3rd monht under column 1st
revenue of 3rd month + revenue of 4th month = 12+16 = 28
this is for column 1
now for 2nd column
under column heading '2' for 1st month
revenue of 1st month + revenue of 2nd month+revenue of 3rd month
= 10+15+20 = 45
for 2nd monht under column 2nd
revenue of 2nd month + revenue of 3rd month+revenue of 4th month=
=15+20+12 = 47
revenue of 3rd month + revenue of 4th month+revenue of 5th month=
= 20+12+16 = 48
& so on ...
please help me to get this output ...
Ajay
June 30, 2008 at 2:53 am
Maybe not the best way, but the following code works as long as you stick to just having the 6 months revenue....
create table rev
(
id int identity(1,1),
Rev money
)
go
insert into rev (Rev)
values (10)
insert into rev (Rev)
values (15)
insert into rev (Rev)
values (20)
insert into rev (Rev)
values (12)
insert into rev (Rev)
values (16)
insert into rev (Rev)
values (20)
go
select r.id, Rev, (select sum(Rev) from rev where (id between r.id and r.id +1 and r.id <= 6) and r.id + 1 <= 6) as '1',
(select sum(Rev) from rev where (id between r.id and r.id + 2 and r.id <= 6) and r.id + 2 <= 6) as '2',
(select sum(Rev) from rev where (id between r.id and r.id + 3 and r.id <= 6) and r.id + 3 <= 6) as '3',
(select sum(Rev) from rev where (id between r.id and r.id + 4 and r.id <= 6) and r.id + 4 <= 6) as '4',
(select sum(Rev) from rev where (id between r.id and r.id + 5 and r.id <= 6) and r.id + 5 <= 6) as '5'
from rev r
Drop table rev
July 1, 2008 at 6:10 am
Here is a different take on a solution (using the table defined above).
Here I build the vertical table to represent the values, including a depth. Then I create a manual type of a pivot table to format the output as you are asking. But, if you use any type of a report (such as crystal or SSRS), and you use a crosstab object, you would be better off with "SELECT * FROM aCTEtable" as the select after the CTE, as this will not limit the number of columns (months) in your SQL.
WITH aCTEtable AS
(SELECT id, Rev, 1 AS Depth
FROM rev A
UNION ALL
SELECT A.id, A.Rev+B.Rev, B.Depth + 1
FROM rev A
JOIN aCTEtable B
ON A.id = B.id+1)
SELECT id-Depth+1
, SUM(CASE Depth WHEN 1 THEN Rev END) AS month1
, SUM(CASE Depth WHEN 2 THEN Rev END) AS month2
, SUM(CASE Depth WHEN 3 THEN Rev END) AS month3
, SUM(CASE Depth WHEN 4 THEN Rev END) AS month4
, SUM(CASE Depth WHEN 5 THEN Rev END) AS month5
, SUM(CASE Depth WHEN 6 THEN Rev END) AS month6
FROM aCTEtable
GROUP BY id-Depth+1
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply