1

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
  • There are lots of guides specific to each RDBMS that will explain the syntax for SQL PIVOT functionality. Might try a search engine for this one. – Dave Aug 7 '15 at 15:09
  • 1
    You should always specify your RDBMS when asking questions. The syntax may vary considerably depending on vendor. – Vérace Aug 7 '15 at 15:15
  • I tagged it cache assuming that was the tag for Caché. ¯_(ツ)_/¯ – Devil's Advocate Aug 7 '15 at 16:23
  • What is Cache? Is it this one: Caché? The "cache" tag has no explanation and I think most people would assume that the tag is about caching. If you indeed using this (not well known) dbms, it would be better if you added a link in your question as well. – ypercubeᵀᴹ Aug 7 '15 at 16:32
  • I thought it was more well known than it is. Sorry. db-engines.com/en/system/Cach%C3%A9 – Devil's Advocate Aug 7 '15 at 16:40
3

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.

  • So this worked, but only doing two months took 2.7 hours to run. I think that's more our environment than the query though. Thanks! – Devil's Advocate Aug 10 '15 at 12:35
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.