Crosstab query

Question

Crosstab query

Viewing 9 posts - 16 through 23 (of 23 total)

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

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

Here is the data and code as copied from the thread. Please note the expected results are wrong for ram!!

Eswin (7/2/2009)
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..............

Here is the code I wrote:

/* -- Incorrect expected results --

Teacher Jan feb march

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

joe 0 2 1

mary 1 0 1

ram 2 0 0

sara 1 1 1

*/

Create TABLE dbo.SomeTable1

(

SchoolDate datetime,

Teacher varchar(5),

Student varchar(3),

Class char(1) );

INSERT INTO dbo.SomeTable1

(SchoolDate, 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';

select

Teacher,

sum(case when dateadd(mm, datediff(mm, 0, SchoolDate), 0) = '2009-01-01' then 1 else 0 end) as Jan,

sum(case when dateadd(mm, datediff(mm, 0, SchoolDate), 0) = '2009-02-01' then 1 else 0 end) as Feb,

sum(case when dateadd(mm, datediff(mm, 0, SchoolDate), 0) = '2009-03-01' then 1 else 0 end) as Mar

from

dbo.SomeTable1

group by

Teacher

order by

Teacher;

drop table dbo.SomeTable1;

Care to change the requirements again Eswin??

Eswin SSCrazy Eights Points: 8320 More actions · Answer 2

Why are you pulling my leg........

Sorry Lynn had made a mistake............Thanks a lot for the help

Tanx 😀

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

Eswin (7/2/2009)
Why are you pulling my leg........
Sorry Lynn had made a mistake............Thanks a lot for the help

Excuse me??? I made a mistake???

I'm sorry, but I don't appreciate being called a liar on a public forum!!

Eswin SSCrazy Eights Points: 8320 More actions · Answer 4

Eswin (7/2/2009)
Why are you pulling my leg........
Sorry Lynn had made a mistake............Thanks a lot for the help

Sorry Lynn , I had made a mistake........

Didn't mean to offend you.

Thanks a lot for the help............

Tanx 😀

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

Heh... I can see another "etiquitte" article coming out of this thread. 😉

I'm going to go to the original thread and see if anything can be salvaged. The OP is a first time poster and may not understand the need for providing data and table structure in a readily consumable format. People tend to be a bit short in explaining why and the OP may not have a clue as to what they're talking about.

Eswin, your idea of trying to help by taking on the original problem as your own was certainly an honorable one. I strongly value the trait of people taking "ownership" of a problem... you just didn't do it the right way. Hopefully, this thread is a lesson learned for all of us.

Lynn, just so you know... I'd have reacted in just about the same way.

--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)

Eswin SSCrazy Eights Points: 8320 More actions · Answer 6

Eswin

SSCrazy Eights

Points: 8320

July 5, 2009 at 10:46 pm

#1020202

Thanks a lot guyz...................

Tanx 😀

ramdas.narayanan Ten Centuries Points: 1214 More actions · Answer 7

Here is the query,

In the result Ram has one class in feb as per the data...

SELECT teacher,

Jan=SUM(CASE WHEN DATEPART(m,Date)=1 THEN 1 ELSE 0 END),

Feb=SUM(CASE WHEN DATEPART(m,Date)=2 THEN 1 ELSE 0 END),

March=SUM(CASE WHEN DATEPART(m,Date)=3 THEN 1 ELSE 0 END)

FROM SomeTable1

Group BY teacher

teacherJanFebMarch

joe021

mary101

ram210

sara111

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

ramdas.narayanan (7/7/2009)
Here is the query,
In the result Ram has one class in feb as per the data...
SELECT teacher,
Jan=SUM(CASE WHEN DATEPART(m,Date)=1 THEN 1 ELSE 0 END),
Feb=SUM(CASE WHEN DATEPART(m,Date)=2 THEN 1 ELSE 0 END),
March=SUM(CASE WHEN DATEPART(m,Date)=3 THEN 1 ELSE 0 END)
FROM SomeTable1
Group BY teacher
teacherJanFebMarch
joe021
mary101
ram210
sara111

Works great for the sample data, but what happens if you have multiple years in your data and you forget to restrict the data in the where clause? Also, iirc, the assignment format for column aliases has been depreciated and may no longer be supported in future versions of SQL Server. I recommend moving away from that coding style.

ramdas.narayanan Ten Centuries Points: 1214 More actions · Answer 9

Hi Lynn,

Thanks for the tips, really appreciate it. I guess i was coding for the specific instance. For the assignment i need to use the AS clause.