Need Help with a Pivot/Cross-tab query

pgraber · #1 (**permalink**) 03-03-06, 13:59

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!

LKBrwn_DBA · #2 (**permalink**) 03-03-06, 14:55

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.

pgraber · #3 (**permalink**) 03-03-06, 15:08

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

LKBrwn_DBA · #4 (**permalink**) 03-03-06, 15:23

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;

Pat Phelan · #5 (**permalink**) 03-03-06, 15:27

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

pgraber · #6 (**permalink**) 03-03-06, 15:43

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

Using MySQL

r937 · #7 (**permalink**) 03-03-06, 22:14

moving to mysql forum

r937 · #8 (**permalink**) 03-03-06, 22:19

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

Internet Services

Web Development

Digital Marketing

Consumer Tech