Group by month in result columns?

Question

I'm using this DBMS: Caché

I've never used a pivot table in SQL and I'm not even good at it in Excel, but I assume that's what I need. Given the following sample data:

ID    Name    Result    Date
01    Steve   Win       2015-01-06 00:00:00
02    James   Win       2015-05-12 00:00:00
03    Bob     Lose      2015-06-21 00:00:00
04    Bob     Win       2015-05-23 00:00:00
05    James   Win       2015-04-04 00:00:00
06    James   Lose      2015-01-06 00:00:00
...

How can I get results like this? (numbers don't match sample data)

Wins By Month

        Jan    Feb    Mar
Steve   4      0      1
James   1      3      1
Bob     3      1      1
Steve   2      3      2

Answer 1

A PIVOT that will work in most DBMS is:

select name
     , count(case when Result = 'Win' and Month(dt) = 1 then 1 end) as Jan
     , count(case when Result = 'Win' and Month(dt) = 2 then 1 end) as Feb
     , count(case when Result = 'Win' and Month(dt) = 3 then 1 end) as Mar  
from t 
group by name

I used dt as name for your date column since date is a reserved word. As others have mentioned, there may be better solutions for a specific vendor so you should always tag your question with that, unless you are looking for a general solution.

Answer 2

The pivot of this would look something like the code example below. @Lennart's answer is much simpler as you can easily see.

USE [tempdb];
GO

IF OBJECT_ID('test' , 'U') IS NOT NULL
BEGIN
    DROP TABLE [dbo].[test]
END;
CREATE TABLE [dbo].[test]
(
    [ID]     INT IDENTITY NOT NULL
  , [Name]   CHAR(5) NOT NULL
  , [Result] CHAR(4) NOT NULL
  , [Date]   DATETIME NOT NULL  
) ON [PRIMARY];
GO

INSERT INTO [dbo].[test]
VALUES
      ( 'Steve' , 'Win ' , '2015-01-06 00:00:00.000' )
    , ( 'James' , 'Win ' , '2015-05-12 00:00:00.000' )
    , ( 'Bob  ' , 'Lose' , '2015-06-21 00:00:00.000' )
    , ( 'Bob  ' , 'Win ' , '2015-05-23 00:00:00.000' )
    , ( 'James' , 'Win ' , '2015-04-04 00:00:00.000' )
    , ( 'James' , 'Lose' , '2015-01-06 00:00:00.000' );
GO

SELECT * FROM [dbo].[test];
GO

SELECT
       [Name]
     , COALESCE( [Jan] , 0) AS [Jan]
     , COALESCE( [Feb] , 0) AS [Feb]
     , COALESCE( [Mar] , 0) AS [Mar]
     , COALESCE( [Apr] , 0) AS [Apr]
     , COALESCE( [May] , 0) AS [May]
     , COALESCE( [Jun] , 0) AS [Jun]
FROM
(
SELECT 
      [Name] 
    , CASE WHEN [Result] = 'Win'
           THEN 1 
           ELSE 0 
      END AS [Wins]
    , LEFT( DATENAME( MONTH , [Date] ) , 3 ) AS [Month]
FROM 
    [dbo].[test]
WHERE 
    [Result] = 'Win'
GROUP BY 
      [Name]
    , CASE WHEN [Result] = 'Win' 
           THEN 1 
           ELSE 0 
      END 
    , LEFT( DATENAME( MONTH , [Date] ) , 3 ) 
) AS SourceTable
PIVOT
(
    SUM([Wins])
    FOR [Month] IN ( [Jan] , [Feb] , [Mar] , [Apr] , [May] , [Jun] )
) AS PivotTable;
GO