Hi can anyone help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Hi can anyone help

Author

Topic

Noejfah
Starting Member

5 Posts

Posted - 2013-05-30 : 11:29:09

HI I have table that looks like this:

Month SUM_of_v_0 SUM_of_v_1 SUM_of_v_2 SUM_of_v_3 SUM_of_v_4 SUM_of_v_5 SUM_of_v_6 SUM_of_v_7 SUM_of_v_8 SUM_of_v_9 SUM_of_v_10 SUM_of_v_11
May-12 239 820 3233 6513 8903 11093 12601 14033 15673 16680 18072 20189
Jun-12 26 74 283 1277 3021 4562 5632 6632 7837 8678 9621 11028
Jul-12 37 13 24 151 784 2027 3008 3809 4719 5383 6171 7159
Aug-12 43 37 16 17 123 705 1855 2868 3836 4466 5225 6274

I need the the follwing result:basicall the 1st month remains the same but for the second month it moves 1 month forward, for the 2nd month it moves 2 months forward etc:
Month SUM_of_v_0 SUM_of_v_1 SUM_of_v_2 SUM_of_v_3 SUM_of_v_4 SUM_of_v_5 SUM_of_v_6 SUM_of_v_7 SUM_of_v_8 SUM_of_v_9 SUM_of_v_10 SUM_of_v_11
May-12 239 820 3233 6513 8903 11093 12600 14032 15670 16779 18770 20186
Jun-12 74 283 1277 3021 4562 5631 6631 7836 8743 9962 11029
Jul-12 24 150 783 2026 3006 3807 4717 5442 6338 7155
Aug-12 17 123 704 1851 2864 3828 4504 5406 6264

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-30 : 11:58:37

Is this what you need?

[CODE]

DECLARE @foo table(e_dt date,amt1 integer, amt2 integer, amt3 integer, amt4 integer, amt5 integer, amt6 integer)

insert into @foo values('1/1/2013',1,2,3,4,5,6)
insert into @foo values('2/1/2013',1,2,3,4,5,6)
insert into @foo values('3/1/2013',1,2,3,4,5,6)
insert into @foo values('4/1/2013',1,2,3,4,5,6)
insert into @foo values('5/1/2013',1,2,3,4,5,6)
insert into @foo values('6/1/2013',1,2,3,4,5,6)

SELECT e_dt, (CASE WHEN RN = 1 THEN amt1
WHEN RN = 2 THEN amt2
WHEN RN = 3 THEN amt3
WHEN RN = 4 THEN amt4
WHEN RN = 5 THEN amt5
WHEN RN = 6 THEN amt6
ELSE NULL END) as Amt1,
(CASE WHEN RN = 1 THEN amt2
WHEN RN = 2 THEN amt3
WHEN RN = 3 THEN amt4
WHEN RN = 4 THEN amt5
WHEN RN = 5 THEN amt6
ELSE NULL END) as Amt2,
(CASE WHEN RN = 1 THEN amt3
WHEN RN = 2 THEN amt4
WHEN RN = 3 THEN amt5
WHEN RN = 4 THEN amt6
ELSE NULL END) as Amt3,
(CASE WHEN RN = 1 THEN amt4
WHEN RN = 2 THEN amt5
WHEN RN = 3 THEN amt6
ELSE NULL END) as Amt4,
(CASE WHEN RN = 1 THEN amt5
WHEN RN = 2 THEN amt6
ELSE NULL END) as Amt5,
(CASE WHEN RN = 1 THEN amt6
ELSE NULL END) as Amt6
FROM
(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) as RN, * from @foo) A

[/CODE]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources