Mssql 2012 : Pivot Typologies And Dates Using Php

I don't understand correctly how pivot is working with MSSQL, and I'm not able to apply pivot with my needs. I have a MSSQL 2012 Database with a 'typology' content : Date (Unix) |

Solution 1:

You're not setting your @cols value correctly for one..

should look something like this.

DECLARE@cols NVARCHAR (MAX)
SELECT@cols=COALESCE (@cols+',', '') + QUOTENAME(CONVERT(NVARCHAR, [DATE], 106))
FROM    (SELECTDISTINCT [DATE] FROM #TABLE) PV  
ORDERBY [DATE]

if you're still getting errors. use PRINT @query before EXEC SP_EXECUTESQL @query to see what your query looks like and try to debug it fully formed. Post your result back so we can see what the final query looks like.

Since you only have 2 fields in your pivoted query, use the aggregate on the date field if you want to see the type_1 field..

PIVOT (
    COUNT([DATE]) FOR [DATE] IN ('+@cols+')
) p