SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Crosstab query


Crosstab query

Author
Message
Eswin
Eswin
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1939 Visits: 1078
Hi,

TABLE SomeTable1
(
Year SMALLINT,
teacher varchar(5),
student varchar(3),
class char(1) )

select * from SomeTable1 give

year teacher student class
2008 mary a.a a
2008 mary a.b b
2008 ram a.c a
2008 mary a.d b
2007 mary b.a c
2007 ram b.b a
2007 sara b.c b
2007 joe b.d c
2006 mary a.e a
2006 sara b.c a
2006 joe a.e b
2006 mary a.a a
2006 mary a.b b
2006 ram a.c a
2007 mary a.d b
2008 mary b.a c
2008 ram b.b a
2008 sara b.c b
2008 joe b.d c
2007 mary a.e a
2007 sara b.c a
2007 joe a.e b
2007 mary a.a a
2007 mary a.b b
2007 ram a.c a
2005 mary a.d b
2005 mary b.a c
2005 ram b.b a
2005 sara b.c b
2006 joe b.d c
2006 mary a.e a
2005 sara b.c a
2005 joe a.e b

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 :-D
Randolph West
Randolph West
Old Hand
Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)

Group: General Forum Members
Points: 362 Visits: 437
SQL 2005 includes the PIVOT keyword, which you can use in combination with SUMs and COUNTs and so on.
Mark Cowne
Mark Cowne
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3083 Visits: 24042
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
Florian Reischl
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3441 Visits: 3934
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/


The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Christopher Stobbs
Christopher Stobbs
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2412 Visits: 2233
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
Numbers / Tally Tables

SQL-4-Life
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84699 Visits: 41069
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
David Webb-CDS
David Webb-CDS
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1554 Visits: 8586
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
Florian Reischl
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3441 Visits: 3934
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.


The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38933 Visits: 38508
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

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Eswin
Eswin
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1939 Visits: 1078
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 :-D
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search