Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2006
    Posts
    3

    Unanswered: Need Help with a Pivot/Cross-tab query

    I have looked everywhere for an explanation; I'm stumped. Here's the problem:

    Querying only 1 table.

    Here's the basic table:

    Year ----------AvgListPrice--------------SqFoot
    2006 --------------2----------------------8
    2007 ------------- 4-----------------------9
    2008--------------3-----------------------4


    I want the query to return results like this:

    ------------------- 2006----------- 2007-----------2008
    AvgList Price---------2----------------4 ------------- 3
    SqFoot ------------- 8--------------- 9 --------------4


    This is only a couple of fields in the table; there are more. I've tried my things with CASE, IF, to no avail.

    Any help or insight would be greatly appreciated. Thanks!

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    If your DBMS is NOT MS Access or SQL2k, then you can dump the table data to a CSV file and either load into an Excel spreadsheet or MS Access table and generate the pivot report.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Mar 2006
    Posts
    3

    Building a Web-app / Neeed an SQL soluton

    I am using the query and its results for a webpage. The data will not be exported.

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Then you will have to "pivot" the data yourself based on a query similar to this:
    Code:
    SELECT year, SUM(AvgListPrice) AvgListPrice, SUM(SqFoot) SqFoot 
      FROM TheTable GROUP BY year;

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is a job that is much better handled on the client side when possible. Depending on your web server environment (Cold Fusion, IIS/ASP, other) you probably have some client side tool that will do this both more efficiently and with less coding effort than trying to do it using SQL.

    -PatP

  6. #6
    Join Date
    Mar 2006
    Posts
    3

    Pivot Followup

    I just tried that, yet it doesn't give me the inddividual year colums; There are 3 years.

    Using MySQL

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    moving to mysql forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the brute force way to get what you want is like this --
    Code:
    select 'AvgList Price'
         , sum(case when Year=2006
                    then AvgListPrice
                    else 0 end)   as '2006'
         , sum(case when Year=2007
                    then AvgListPrice
                    else 0 end)   as '2007'
         , sum(case when Year=2008
                    then AvgListPrice
                    else 0 end)   as '2008'
      from daTable
    union all
    select 'SqFoot'
         , sum(case when Year=2006
                    then SqFoot
                    else 0 end)   as '2006'
         , sum(case when Year=2007
                    then SqFoot
                    else 0 end)   as '2007'
         , sum(case when Year=2008
                    then SqFoot
                    else 0 end)   as '2008'
      from daTable
    but i agree, you really don't want to be doing this with sql
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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