Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Crosstab query Expand / Collapse
Author
Message
Posted Thursday, July 02, 2009 4:47 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 28, 2013 8:50 AM
Points: 1,606, Visits: 1,039
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
Post #746129
Posted Thursday, July 02, 2009 4:57 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, March 04, 2014 2:47 PM
Points: 317, Visits: 405
SQL 2005 includes the PIVOT keyword, which you can use in combination with SUMs and COUNTs and so on.
Post #746141
Posted Thursday, July 02, 2009 6:35 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 6:13 AM
Points: 1,694, Visits: 19,550


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



____________________________________________________

How to get the best help on a forum

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

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #746196
Posted Thursday, July 02, 2009 6:36 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 04, 2012 12:23 PM
Points: 2,087, Visits: 3,932
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
Post #746197
Posted Thursday, July 02, 2009 6:36 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 24, 2010 4:10 AM
Points: 1,553, Visits: 2,232
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


Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
Post #746200
Posted Thursday, July 02, 2009 11:15 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:47 AM
Points: 35,950, Visits: 30,231
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #746493
Posted Thursday, July 02, 2009 11:55 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 5:00 PM
Points: 893, Visits: 6,871
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
Post #746521
Posted Thursday, July 02, 2009 12:04 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 04, 2012 12:23 PM
Points: 2,087, Visits: 3,932
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
Post #746530
Posted Thursday, July 02, 2009 12:07 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:07 AM
Points: 22,473, Visits: 30,142
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



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)
Post #746533
Posted Thursday, July 02, 2009 9:10 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 28, 2013 8:50 AM
Points: 1,606, Visits: 1,039
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
Post #746738
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse