SQL Server pivot challenge

Question

there are two given columns - prefix and datetime, e.g.

PREFIX | DATETIME

ABC    | 2010-01-28 07:11:31.000

DEF    | 2010-02-15 07:11:31.000

DEF    | 2010-01-25 07:11:31.000

I want to Pivot the data by year/month and prefix. The result should look like this:

YEAR   | MONTH | ABC | DEF |

2010   | Jan   |  1  |  1  |

2010   | Feb   |  0  |  1  |

In other words, count how much objects with the same prefix has the same period (year/month).

I tried this code, but it doesn't work and I do not know where is the problem.

DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(O.OU01) 
                from Test.dbo.Organisation as O
                group by O.OU01
                order by O.OU01
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

set @query = 'SELECT [Year], [month], ' + @cols + ' 
        from 
        (
           SELECT Year(A.Stand) Year,
             Datename(month, A.Stand) [month],
             O.OU01
           FROM Test.dbo.Assets as A
           join Test.dbo.Organisation as O on A.Key = O.Key
        ) x
        pivot 
       (
            Count([O.OU01])
            for O.OU01 in (' + @cols + ')
        ) p '

execute(@query)

Answer 1

Use Conditional Aggregate

SELECT Year([DATETIME]) [Year],
       Datename(month, [DATETIME]) [month],
       Count(CASE WHEN PREFIX = 'ABC' THEN 1 END) ABC,
       Count(CASE WHEN PREFIX = 'DEF' THEN 1 END) DEF
FROM   Tablename
GROUP  BY Year([DATETIME]),
          Datename(month, [DATETIME]) 

Or using Pivot

SELECT *
FROM   (SELECT Year([DATETIME])            [Year],
               Datename(month, [DATETIME]) [month],
               PREFIX,
               [DATETIME]
        FROM   tablename) a
       PIVOT (Count([DATETIME])
             FOR PREFIX IN ([ABC],
                            [DEF])) AS P 

SQLFIDDLE DEMO

Dynamic Version:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(PREFIX) 
                    from es
                    group by PREFIX
                    order by PREFIX
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT [Year], [month], ' + @cols + ' 
            from 
            (
               SELECT Year(DATETIMEs)            Year,
                 Datename(month, DATETIMEs) [month],
                 PREFIX
               FROM es
            ) x
            pivot 
            (
                Count([PREFIX])
                for PREFIX in (' + @cols + ')
            ) p '

execute(@query)

Demo