(Un)pivoting tables on SQL

56 views Asked by At

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

1

There are 1 answers

1
James Z On BEST ANSWER

You have:

year IN ([1], <...>, [23])

This is probably supposed to be

year IN ([1993], <...>, [2015])

And then of course those same columns in the select part too:

SELECT concept,
    [1993], 
    <...>,
    [2015]