Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Crosstab query


Crosstab query

Author
Message
Eswin
Eswin
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: 1639 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 (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)

Group: General Forum Members
Points: 318 Visits: 431
SQL 2005 includes the PIVOT keyword, which you can use in combination with SUMs and COUNTs and so on.
Mark Cowne
Mark Cowne
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2086 Visits: 22778
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
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: 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
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: 1574 Visits: 2232
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-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44845 Visits: 39850
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
David Webb-CDS
David Webb-CDS
SSC Eights!
SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)

Group: General Forum Members
Points: 914 Visits: 8584
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
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: 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-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24157 Visits: 37925
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.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: 1639 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