Solved

Crosstab query

Posted on 2003-10-22
2
403 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 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 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
db2 connect on mac 3 542
Procedure to create a text file in DB2 on AS400 15 430
eclipse find/step into stored procedure 3 189
Tsm library capacity monitor warning 5 180
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…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

856 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