Crosstab query

Question

Post reply

Crosstab query

Eswin

SSCrazy Eights

Points: 8320
More actions
July 2, 2009 at 4:47 am

#135860

Hi,
TABLE SomeTable1
(
Year SMALLINT,
teacher varchar(5),
student varchar(3),
class char(1) )
select * from SomeTable1 give
year teacher student class
2008marya.aa
2008marya.bb
2008rama.ca
2008marya.db
2007maryb.ac
2007ramb.ba
2007sarab.cb
2007joeb.dc
2006marya.ea
2006sarab.ca
2006joea.eb
2006marya.aa
2006marya.bb
2006rama.ca
2007marya.db
2008maryb.ac
2008ramb.ba
2008sarab.cb
2008joeb.dc
2007marya.ea
2007sarab.ca
2007joea.eb
2007marya.aa
2007marya.bb
2007rama.ca
2005marya.db
2005maryb.ac
2005ramb.ba
2005sarab.cb
2006joeb.dc
2006marya.ea
2005sarab.ca
2005joea.eb
I want to create a crosstab query something like this
Teacher_name 2006 2007 2008
mary 4 2 3
sara 2 3 3
ram 2 0 0
joe 2 0 1
where numbers under 2006,2007,2008 indicate the count of students taught by that particular teacher for that year.
Please help...........
Tanx 😀

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Randolph West SSCommitted Points: 1585 More actions · Answer 1

SQL 2005 includes the PIVOT keyword, which you can use in combination with SUMs and COUNTs and so on.

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 2

SELECT teacher,

SUM(CASE WHEN Year=2006 THEN 1 ELSE 0 END) AS [2006],

SUM(CASE WHEN Year=2007 THEN 1 ELSE 0 END) AS [2007],

SUM(CASE WHEN Year=2008 THEN 1 ELSE 0 END) AS [2008]

FROM SomeTable1

GROUP BY teacher

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 3

Here are two really good articles about cross tab queries:

http://www.sqlservercentral.com/articles/T-SQL/63681/

http://www.sqlservercentral.com/articles/cross+tab/65048/

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 4

Depending on how you system works you might need to make this dynamic as you may get more years creeping in for example 2009/2010/2011 ect.

if you need more help just shout

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 5

Eswin,

With 495 visits on your part, I'm thinking that it may be time for you to learn how to post readily consumable data so you can save us a bit of time and get tested answers in very short order. Please see the first link in my signature below. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David Webb-CDS SSCoach Points: 17398 More actions · Answer 6

Isn't this the same problem you were working with here:

('http://www.sqlservercentral.com/Forums/FindPost745975.aspx')

?

And then again, I might be wrong ...
David Webb

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 7

David Webb (7/2/2009)
Isn't this the same problem you were working with here:
('http://www.sqlservercentral.com/Forums/FindPost745975.aspx')
?

Good catch David! I left a note in the other therad.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

David Webb (7/2/2009)
Isn't this the same problem you were working with here:
('http://www.sqlservercentral.com/Forums/FindPost745975.aspx')
?

Link as coded above doesn't work. Here is a working link: http://www.sqlservercentral.com/Forums/FindPost745975.aspx

Eswin SSCrazy Eights Points: 8320 More actions · Answer 9

Jeff,

Table:

Create TABLE SomeTable1

(

Date datetime,

teacher varchar(5),

student varchar(3),

class char(1) )

Data:

INSERT INTO SomeTable1

(Date, teacher, student, class)

SELECT '2009/1/1', 'mary', 'a.d','a' UNION ALL

SELECT '2009/1/11', 'ram', 'a.e','b' UNION ALL

SELECT '2009/2/10', 'joe', 'a.e','c'UNION ALL

SELECT '2009/3/12', 'sara', 'a.d','a' UNION ALL

SELECT '2009/3/19', 'mary', 'a.f','b' UNION ALL

SELECT '2009/3/13','joe', 'a.d','c' UNION ALL

SELECT '2009/2/17', 'ram', 'a.e','c' UNION ALL

SELECT '2009/2/21', 'joe', 'a.f','b' UNION ALL

SELECT '2009/2/12', 'sara', 'a.f','a' UNION ALL

SELECT '2009/1/23', 'sara', 'a.d','b' UNION ALL

SELECT '2009/1/14', 'ram', 'a.e','a'

I need to make a query that returns something like this , where values under Jan , Feb , March represent no:of students attended the classes of respective teacher for that month.

Teacher Jan feb march

------ ------- ------- -------

joe 0 2 1

mary 1 0 1

ram 2 0 0

sara 1 1 1

Please help..............

Tanx 😀

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

Really? That's it? How about this:

create TABLE dbo.SomeTable12

(

SchoolYear SMALLINT,

Teacher varchar(5),

Student varchar(3),

Class char(1) );

INSERT INTO dbo.SomeTable12

(SchoolYear, Teacher, Student, Class)

SELECT 2006, 'mary', 'a.d','a' UNION ALL

SELECT 2006, 'ram', 'a.e','b' UNION ALL

SELECT 2006, 'joe', 'a.e','c'UNION ALL

SELECT 2006, 'sara', 'a.d','a' UNION ALL

SELECT 2007, 'mary', 'a.f','b' UNION ALL

SELECT 2007,'joe', 'a.d','c' UNION ALL

SELECT 2007, 'ram', 'a.e','c' UNION ALL

SELECT 2007, 'joe', 'a.f','b' UNION ALL

SELECT 2008, 'sara', 'a.f','a' UNION ALL

SELECT 2008, 'sara', 'a.d','b' UNION ALL

SELECT 2008, 'ram', 'a.e','a';

select

*

from

dbo.SomeTable12;

select

Teacher,

sum(case when SchoolYear = 2006 then 1 else 0 end) as Yr2006,

sum(case when SchoolYear = 2007 then 1 else 0 end) as Yr2007,

sum(case when SchoolYear = 2008 then 1 else 0 end) as Yr2008

from

dbo.SomeTable12

group by

Teacher

order by

Teacher;

drop table SomeTable12;

Eswin SSCrazy Eights Points: 8320 More actions · Answer 11

Christopher Stobbs (7/2/2009)
Depending on how you system works you might need to make this dynamic as you may get more years creeping in for example 2009/2010/2011 ect.
if you need more help just shout

How do i make it dynamic.....

Tanx 😀

Eswin SSCrazy Eights Points: 8320 More actions · Answer 12

Lynn Pettis (7/2/2009)
Really? That's it? How about this:

No my date datatype is "datetime"

Tanx 😀

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

Eswin (7/2/2009)
Christopher Stobbs (7/2/2009)
Depending on how you system works you might need to make this dynamic as you may get more years creeping in for example 2009/2010/2011 ect.
if you need more help just shout
How do i make it dynamic.....

I'll leave that as an exercise for you. I'll give you a hint, look at the articles I have referenced in my signature block. Pay close attention to the last two links at the bottom regarding Cross Tabs and Pivots.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

Eswin (7/2/2009)
Lynn Pettis (7/2/2009)
Really? That's it? How about this:
No my date datatype is "datetime"

It may be now, but it wasn't at the time I copied down your code. If you look, you editted your post AFTER I had posted my solution. Don't change the criteria mid stream, and then say I didn't meet your requirements. You'll find that a quick way to tick people off.

You want to change the requirements, you should have have posted the new code in a new post instead of modifying an old one.