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)

what have you tried? Unless your prefixes are predefined (and a pretty small number of them) you'll likely need to go with a dynamic sql solution see http://stackoverflow.com/questions/2922797/t-sql-pivot-possibility-of-creating-table-columns-from-row-values as an example — Kritner, Dec 22 '14 at 13:38
no, the prefix is not predefined. I can not hardcode the prefix in the query — Sven Müller, Dec 22 '14 at 14:01
@bluefeet Msg 207, Level 16, State 1, Line 12 Invalid column name 'O.OU01'. Msg 107, Level 15, State 1, Line 13 The column prefix 'O' does not match with a table name or alias name used in the query. — Sven Müller, Dec 22 '14 at 14:41
@SvenMüller remove the `O.` alias inside your pivot - change the code to `Count([OU01]) for OU01 in` — Taryn, Dec 22 '14 at 14:42
@bluefeet Do you see a chance to order the result first by year and then by month? — Sven Müller, Dec 22 '14 at 14:49

score 2 · Accepted Answer · edited Dec 22 '14 at 14:05

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

@SvenMüller I've added a dynamic version for you. NoDisplayName, hope you don't mind that I added that to your answer. :) — Taryn, Dec 22 '14 at 14:06
@bluefeet- How would i Mind sir. Thanks for the Dynamic pivot version Pivot Guru :) I learned pivot concept from your answers sir — Pரதீப், Dec 22 '14 at 15:41

score 1 · Answer 2 · answered Dec 22 '14 at 13:40

1

Use SQL Server's PIVOT TABLE

Try this:

SELECT Year, MonthName, [ABC], [DEF]
FROM (SELECT YEAR(A.DATETIME) AS Year, DATENAME(A.DATETIME,Getdate()) AS MonthName, 
             A.PREFIX  AS Header, A.DATETIME DATA 
        FROM tableA A
     ) AS A 
PIVOT(COUNT(DATA) FOR Header IN ([ABC], [DEF])) AS P

answered Dec 22 '14 at 13:40

Saharsh Shah

28,687
8
48
83

Missing a `,` after `AS MonthName` – T I Dec 22 '14 at 13:42
the prefix is dynamic, I can not hardcode it in the query – Sven Müller Dec 22 '14 at 13:58

SQL Server pivot challenge

2 Answers2

SQLFIDDLE DEMO