Search code examples
sqlt-sqlpivotunpivot

Unpivot columns from another table

I have used Unpivot to get data from a table I am trying to manipulate. I use this query to rearrange my columns to rows;

SELECT        Id, ownername, ownervalue
FROM            Contacts UNPIVOT (ownervalue FOR ownername IN (column1, column2, column3)) unpiv;

This works great. However I would prefer to get my column names from another table instead of hard-coding them in the query. Ideally i would like this, but it does not work;

SELECT        Id, ownername, ownervalue
FROM            Contacts UNPIVOT (ownervalue FOR ownername IN (SELECT * FROM ColumnsTable)) unpiv;

Is it possible to get my list of columns from another table like this?

Solution

  • As far as I know (please correct me if I'm wrong) it is not possible to use dynamic columnnames without the use of a dynamic query, which is executed with for example exec.

    Take a look at the following question unpivot with dynamic columns plus column names