Dynamically column names from select sql

Question

I have the following data output from my database

Observation             1   aug -2015
Improvement suggestion  1   dec -2015
Observation             1   dec -2015
Accident                2   jan -2016
Non Conformity          5   jan -2016
Observation             5   jan -2016

I've tried to figure out how to use PIVOT-table to make this work but cannot make it work. The date is dynamic depending on a date in the database. The output I am looking for is like below. Can someone please point me into right direction?

Look at the fiddle what I've tried so far I know it is using SUM right now, and that is not correct, but I cannot figure out what to use. http://sqlfiddle.com/#!3/0bd0c/4

PS. The data and the image are not related, so don't be fooled by the image. It is just an example

Answer 1

Is this what you were looking for:

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

select @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(ColumnName) 
    from tempData
    group by ColumnName, name
FOR XML PATH(''), Type
).value('.', 'NVARCHAR(MAX)') 
,1,1,'')

set @query = N'SELECT Name, ' + @cols + N' from 
 (
    select Name, value, ColumnName
    from tempData
) x
pivot 
(
    SUM(value)
    for ColumnName in (' + @cols + N')
) p '

exec sp_executesql @query;

Changing this in your fiddle return the rows as you need it.