Crss tab qury - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Crss tab qury

Author

Topic

konark
Yak Posting Veteran

60 Posts

Posted - 2009-06-16 : 13:59:25

rollno section time score
101 1A 2007 65
101 1A 2008 80
101 1A 200901 70
101 1A 200902 90
101 1A 200903 80
101 1A 200904 90
101 1A 200905 70
102 2C 2007 65
102 2C 2008 80
102 2C 200901 70
102 2C 200902 90
102 2C 200903 80
102 2C 200904 90
102 2C 200905 70
103 3A 2007 65
103 3A 2008 80
103 3A 200901 70
103 3A 200902 90
103 3A 200903 80
103 3A 200904 90
103 3A 200905 70
105 3D 2007 65
105 3D 2008 80
105 3D 200901 70
105 3D 200902 90
105 3D 200903 80
105 3D 200904 90
105 3D 200905 70

need a cross tab query

rollNo Section year-2 year-1 month1 month2 month3 month4 month5 month6 month7 month8 month9 month10 month11 month12
101 1A 65 80 70 90 80 90 70 0 0 0 0 0 0

i tried like this

select
(case when c= datepart(year, DATEADD(year,-2,GETDATE())) then d else null )as YearN2,
(case when c= datepart(year, DATEADD(year,-1,GETDATE())) then d else null )as YearN1,
(case when c= CAST(YEAR(getdate()) AS VARCHAR(4)) + '01' then d else null ) as month1,
(case when c= CAST(YEAR(getdate()) AS VARCHAR(4)) + '02' then d else null ) as month2,
(case when c= CAST(YEAR(getdate()) AS VARCHAR(4)) + '03' then d else null ) as month3,
(case when c= CAST(YEAR(getdate()) AS VARCHAR(4)) + '04' then d else null ) as month4,
(case when c= CAST(YEAR(getdate()) AS VARCHAR(4)) + '05' then d else null ) as month5,
(case when c= CAST(YEAR(getdate()) AS VARCHAR(4)) + '06' then d else null ) as month6,
(case when c= CAST(YEAR(getdate()) AS VARCHAR(4)) + '07' then d else null ) as month7,
(case when c= CAST(YEAR(getdate()) AS VARCHAR(4)) + '08' then d else null ) as month8,
(case when c= CAST(YEAR(getdate()) AS VARCHAR(4)) + '09' then d else null ) as month9,
(case when c= CAST(YEAR(getdate()) AS VARCHAR(4)) + '10' then d else null ) as month10,
(case when c= CAST(YEAR(getdate()) AS VARCHAR(4)) + '11' then d else null ) as month11,
(case when c= CAST(YEAR(getdate()) AS VARCHAR(4)) + '12' then d else null ) as month12

from

(
-- i ve the result set here as the above example
)

Chandragupta Mourya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-16 : 14:07:42

will the time values be static (same) always?

konark
Yak Posting Veteran

60 Posts

Posted - 2009-06-16 : 14:10:13

did not understand your question. The case statement i used to filter the last 2 years , and all months of this year.

Chandragupta Mourya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-16 : 14:12:54

but wont the year/month values vary ? will it be always current year month & previous two years?

konark
Yak Posting Veteran

60 Posts

Posted - 2009-06-16 : 14:14:23

yes .

Chandragupta Mourya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-16 : 14:23:05

[code]SELECT rollno,section,
SUM(CASE WHEN time=YEAR(GETDATE())-2 THEN score ELSE 0 END) AS year1,
SUM(CASE WHEN time=YEAR(GETDATE())-1 THEN score ELSE 0 END) AS year2,
SUM(CASE WHEN time=DATENAME(yyyy,GETDATE())+ '01' THEN score ELSE 0 END) AS month1,
SUM(CASE WHEN time=DATENAME(yyyy,GETDATE())+ '02' THEN score ELSE 0 END) AS month2,
SUM(CASE WHEN time=DATENAME(yyyy,GETDATE())+ '03' THEN score ELSE 0 END) AS month3,
...
SUM(CASE WHEN time=DATENAME(yyyy,GETDATE())+ '12' THEN score ELSE 0 END) AS month12 FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY rollno,section ORDER BY time) AS Seq,* FROM YourTable)t
GROUP BY rollno,section
[/code]

konark
Yak Posting Veteran

60 Posts

Posted - 2009-06-16 : 17:23:34

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-18 : 02:42:16

quote:
Originally posted by konark

.

What did you want to say?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources