Solved

Crosstab query

Posted on 2003-10-22
2
392 Views
Last Modified: 2008-07-03
I need to create a query that will show the last N months of data for each person in the database.  Here's a simplied version of what my table would look like:

create table test_table (
      personid int,
      value int,
      cdate date)


personid value cdate
-------- ----- ----------
1        100   08/01/2003
1        200   09/01/2003
1        300   10/01/2003
2        400   08/01/2003
2        500   09/01/2003
2        600   10/01/2003

Let's say that I just want to show the last 3 months of data.  Here's what I would like the query to return:

personid  08-2003   09-2003   10-2003
--------  -------   -------   -------
1         100       200       300
2         400       500       600

I actually don't even need the columns to read like shown.  They could be like this:

personid  date1     date2     date3
--------  -----     -----     -----

Anyone have any ideas???



0
Comment
Question by:jgaull
2 Comments
 
LVL 32

Expert Comment

by:shalomc
ID: 9600287
select d0.personid, d0.value as date0_value , d1.value as date1_value, d2.value as date2_value

from test_table d0
join ( select personid, value from test_table where cdate=today()-1 month ) d1
on d1.personid=d0.personid
join ( select personid, value from test_table where cdate=today()-2 month ) d2
on d0.personid=d2.personid


0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 9615571
this is the general format for a pivot query...

use group by
and an aggregation function in the select   e.g. Min,max,sum, ....
with a case statement inside the brackets to identify when the value should be "exposed" for
that column...)    

select
personid,
sum(case when month(cdate) = month(current date) and year(cdate)  = year(current date) then value else null end) as Current
,sum(case when month(cdate) = month(current date - 1 month)  and year(cdate)  = year(current date - 1 month) then value else null end) as lastMonth
,sum(case when month(cdate) = month(current date - 2 month)  and year(cdate)  = year(current date - 2 month) then value else null end) as "2Monthsago"
from test_table
group by personid

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now