Dynamic column names from select sql

Question

I have the following data 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 tried to figure out how to use the PIVOT table to do this work, but cannot make it work. The date is dynamic, depending on the date in the database. The result I'm looking for is similar to below. Can someone point me in the right direction?

Look at the violin that I have tried so far. I know that I am using SUM now, and this is not true, but I cannot figure out what to use. http://sqlfiddle.com/#!3/0bd0c/4

PS. Data and image are not related, so do not fool the image. This is just an example.

Answer 1

, :

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;

.

Answer 2

:

DECLARE @cols AS NVARCHAR(MAX)=
STUFF(
(
    SELECT DISTINCT ',[' + ColumnName + ']'
    FROM tempData
    FOR XML PATH('')
)
,1,1,'');

DECLARE @SqlCmd VARCHAR(MAX)=
'SELECT p.*
 FROM
 (
    SELECT *
    FROM tempData
 ) AS tbl
 PIVOT
 (
    SUM(Value) FOR ColumnName IN(' +  @cols +')
 ) AS p';

 EXEC(@SqlCmd);