Transform rows into columns MS SQL

问题:

I have two tables, tblColumnNames and tblValues with following structure and example values:

tblColumnNames - has two columns with names 'id' and 'ColumnName' 
and following example data:
-------------------------------
id                  ColumnName

0                   columnName1
1                   columnName2
2                   columnName3


tblValues has three columns 'id', 'ColumnId' and 'Value' with following example data:
-------------------------------

id                ColumnId                Value

51                0                       177
52                1                       abcde
53                2                       123
54                0                       40
55                1                       xyz
56                2                       321

ColumnId corresponds to the id in tblColumnNames table.
How to join these two tables so that the resulting columns are the rows of tblColumnNames and respective values are taken from the Value column of tblValues :

result
------------------------------
colummnName1        columnName2         columnName3

177                 abcde               123
40                  xyz                 321

EDIT: The number of rows in tblColumnNames will constantly change.

回答1:

You can try this.

DECLARE @ColNames NVARCHAR(MAX) =''
SELECT @ColNames = @ColNames + '['+ColumnName  +']'+ ', 'FROM tblColumnNames ORDER BY id
SET @ColNames = LEFT(@ColNames, LEN(@ColNames)-1)

DECLARE @TheQuery NVARCHAR(MAX)
SET @TheQuery = 'SELECT PVT.* FROM 
(SELECT V.Value, (V.id - V.ColumnId) RowGroupID, C.ColumnName FROM 
    tblValues V INNER JOIN tblColumnNames C ON V.ColumnId = C.id ) AS SRC 
        PIVOT ( MAX(VALUE) FOR ColumnName IN ('+ @ColNames +')) PVT'

EXEC sp_executesql @TheQuery

Result

RowGroupID  columnName1 columnName2 columnName3
----------- ----------- ----------- -----------
51          177         abcde       123
54          40          xyz         321