• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 430
  • Last Modified:

Crosstab query

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

1 Solution
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

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...)    

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


Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now