Crosstab query

  • 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 😀

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

  • 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
  • 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/

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

  • 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

  • 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.

  • 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

  • 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 😀

  • 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;

  • 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 😀

  • Lynn Pettis (7/2/2009)


    Really? That's it? How about this:

    No my date datatype is "datetime"

    Tanx 😀

  • 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.

  • 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.

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

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