astralmaster astralmaster - 23 days ago 7
SQL Question

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.

Answer Source

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