Sql server 2000 pivot query

Question

Hi everyone. Please forgive me if this has been answered before.

I'm looking for some advice on how to build a pivot or cross tab query.

I have a table that looks likes this

Vessel    Date    Inspector
 A      02/05/10    Morris
 B      05/20/10   Clouseau
 A      07/25/10    Gadget

I need the results to be look like this

Vessel  Jan   Feb      Mar     April   May   June   July   Aug   Sept   Oct    Nov  Dec
  A           Morris                                Gadget
  B                                  Clouseau

Hopefully that makes sense. I'm hoping someone can give me some advice or help to get me started on this.

Thanks

Answer 1

Select Vessel
    , Min ( Case When Month([Date]) = 1 Then Inspector End ) As Jan
    , Min ( Case When Month([Date]) = 2 Then Inspector End ) As Feb
    , Min ( Case When Month([Date]) = 3 Then Inspector End ) As Mar
    ...
    , Min ( Case When Month([Date]) = 12 Then Inspector End ) As Dec
From Table
Group By Vessel

You have to statically declare the columns. It should be noted that this will only work for a single year. If what you are seeking is dynamic column (month) generation, then trying to do it in T-SQL is not the right approach as you can only do it with some fugly dynamic SQL. Instead, you should use a report generator or middle-tier component to build the result set.

Answer 2

I don’t think you need a PIVOT for this — just create 12 subqueries, one for each month:

SELECT
    Vessel,
    (SELECT TOP 1 Inspector FROM Stuff WHERE Vessel=Tbl.Vessel AND MONTH(Date)=1) as Jan,
    (SELECT TOP 1 Inspector FROM Stuff WHERE Vessel=Tbl.Vessel AND MONTH(Date)=2) as Feb,
    (SELECT TOP 1 Inspector FROM Stuff WHERE Vessel=Tbl.Vessel AND MONTH(Date)=3) as Mar,
    (SELECT TOP 1 Inspector FROM Stuff WHERE Vessel=Tbl.Vessel AND MONTH(Date)=4) as Apr,
    (SELECT TOP 1 Inspector FROM Stuff WHERE Vessel=Tbl.Vessel AND MONTH(Date)=5) as May,
    (SELECT TOP 1 Inspector FROM Stuff WHERE Vessel=Tbl.Vessel AND MONTH(Date)=6) as Jun,
    (SELECT TOP 1 Inspector FROM Stuff WHERE Vessel=Tbl.Vessel AND MONTH(Date)=7) as Jul,
    (SELECT TOP 1 Inspector FROM Stuff WHERE Vessel=Tbl.Vessel AND MONTH(Date)=8) as Aug,
    (SELECT TOP 1 Inspector FROM Stuff WHERE Vessel=Tbl.Vessel AND MONTH(Date)=9) as Sep,
    (SELECT TOP 1 Inspector FROM Stuff WHERE Vessel=Tbl.Vessel AND MONTH(Date)=10) as Oct,
    (SELECT TOP 1 Inspector FROM Stuff WHERE Vessel=Tbl.Vessel AND MONTH(Date)=11) as Nov,
    (SELECT TOP 1 Inspector FROM Stuff WHERE Vessel=Tbl.Vessel AND MONTH(Date)=12) as Dec
FROM (
    SELECT DISTINCT(Vessel) FROM Stuff
) Tbl

You can make this work for any particular year by adding a AND YEAR(Date)=2010 (for example) to the end of each of the 12 subqueries.