SQL Pivot Table

A simple pivot query in standard SQL can be written thusly:

select Product,
       sum(case when Month=’Jan’ then Amount else 0 end) Jan,
       sum(case when Month=’Feb’ then Amount else 0 end) Feb,
       sum(case when Month=’Mar’ then Amount else 0 end) Mar
from Sales
group by Product

This query makes the data appear as though there are separate amount columns for each month. SQL Server 2005 introduced the pivot operator:

 
select * from
(Sales pivot (sum(Amount) for Month in (’Jan’, ’Feb’, ’Mar’))

The statement is more succinct while being more expressive. Just for reference, some of my least favorite Java code -

  private String getDateRangeWhereClause(PrescriberReportRequest reportRequest) {
    String where = null;
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    where = " and TO_CHAR(EFECTV_DATE, 'YYYY-MM-dd') >= \'"
      + sdf.format(reportRequest.getRangeFromDate().getTime())
      + "\' and TO_CHAR(EFECTV_DATE, 'YYYY-MM-dd') <= \'"
      + sdf.format(reportRequest.getRangeToDate().getTime()) + "\' ";
    return where;
  }