Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Crosstab query

Posted on 2003-10-22
2
Medium Priority
?
429 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 33

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 2000 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
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…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Suggested Courses

618 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