I'm trying to create a matrix with historical data for some parameters I have in a table. The matter is, when I try to pivot it, won't work. Using the following code
SELECT concept,
[1] '1993',
<...>,
[23] '2015'
FROM
(SELECT concept, YEAR(begin_date) as year, value
FROM params p WITH(NOLOCK)) AS st
PIVOT
(
SUM(value)
FOR year IN ([1], <...>, [23])
) AS pt
will return nothing but a matrix full of NULL values. On the other hand, I've got the correct values using this other query
SELECT pt.year 'Year',
[1],
<...>,
[38]
FROM
(SELECT concept, YEAR(begin_date) as year, value
FROM parameters p WITH(NOLOCK)) AS st
PIVOT
(
SUM(value)
FOR concept IN ([1], <...>, [38])
) AS pt
But then the columns and rows are supposed to be the other way around. What could be the cause of this situation? How could it be solved? Thanks in advance
You have:
This is probably supposed to be
And then of course those same columns in the select part too: