Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2008
    Posts
    23

    Unanswered: Help with pivot\ crosstab query (was "query idea ??")

    consider the following table:
    name , TaskDate
    john , 01/01/2006
    john , 01/03/2005
    steve , 01/05/2006
    i want to build a select statement that gives result like the following:
    name , JanuaryTotal , FebruaryTotal
    john , 150 , 110
    steve , 170 , 50
    so the result will be total tasks in specific month, is it doable in one select or should i turn around using hash tables ???

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hash tables are a bit overkill fella.

    Now - I would google SQL Server crosstabs. The BoL entry for CASE in 2000 was good for this, PIVOT in 2005. I would also think about doing this in your front end (i.e. not in SQL Server). I would also consider whether or not I really want all march 2005's numbers summed with march 2006....

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump
    I would also consider whether or not I really want all march 2005's numbers summed with march 2006....
    oh, not again, maria sharapova...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2008
    Posts
    1

    SQL Server 2-D Matrix Builder

    Quote Originally Posted by akkad
    is it doable in one select or should i turn around using hash tables ???
    Hi,

    If you need to build, from T-SQL, a very complex query that returns a matrix with dynamic columns and rows, you might want to take a look at this new project posted on CodePlex :

    SQL Server 2-D Matrix Builder
    http://www.codeplex.com/SQL2DMatrixBuilder

    This project is basically a SQLCLR stored procedure called TheMatrix, as long as a SQLCLR UDT called QueryBuilder that allow you to describe all the queries you want to call to build the axis and the content of the matrix you need to retrieve back.

    Let me know if you have any issue using this and if you find this useful.

    Thanks,

    Pascal Belaud
    Microsoft

  5. #5
    Join Date
    May 2005
    Posts
    119
    Actually, it is quite simple.

    select name,
    sum(case when month(taskdate)=1 then 1 else 0 end) as January,
    sum(case when month(taskdate)=2 then 1 else 0 end) as February,
    sum(case when month(taskdate)=3 then 1 else 0 end) as March....
    from tablename
    where taskdate >= '1/1/08'
    group by name

    If you wanted to use max/min then you would change the 1/0 to the actual value.

  6. #6
    Join Date
    Jan 2008
    Posts
    23
    thnx Krista327 it is the behaviour needed, but am wondering how to make the columns more dynamic, as i did search i found out that i should use functions, but am not familiar with it, and i dont know how to embedded it with the select statement, so am thinking to add a function (eg: GetColumns(1,2,3,...)) that receives the months which i want them to be the columns and then the function returns the that case statements, how this can be done??

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    http://www.sommarskog.se/dynamic_sql.html
    or the stuff Pascal posted.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •