Converting Rows To Columns With Unknown Number Of Elements

Question

Hello,

I am trying to achive this:

Initial table:

PARM1 |PARM2 |DATE
-------------------
VALUE1|VALUE2|DATE1

VALUE3|VALUE4|DATE2

Final result:

PARM |DATE1 |DATE2 |...
-----------------------
PARM1|VALUE1|VALUE3|...

PARM2|VALUE2|VALUE4|...

Briefly, I want to convert my parameter names into lines and to have a column for every date, where the cells contain the parameter values for the date and parameter.

So far, I managed to get this:

SELECT *
FROM
(
    SELECT [Parameter], [DATE], VALUE
    FROM 
    (
        SELECT PARM1, PARM2 FROM PARAMETER_VALUES
    ) SOURCE_TABLE
    UNPIVOT
    (
        VALOR FOR [Parameter] IN (PARM1, PARM2)
    ) UNPIVOTED_TABLE
) T

The problem is, I can't PIVOT the results now, because I don't know how many DATEs there are. I want it to be dynamic.

Is it possible?

Answer 1

In short, you can't use the PIVOT command with unknown columns.

Your only option is to retrieve the data and reformat, using dynamic SQL or some kind of front end.

Answer 2

You can pivot using dynamic columns, if you build the pivot before hand.

SELECT  @listColYouwantInPivot= STUFF(( SELECT distinct  '], [' + [columnName]
                           FROM     tableName
                         FOR
                           XML PATH('')
                         ), 1, 2, '') + ']'

Just plug @listColYouwantInPivot in the pivot statement with a concatenation afterward.