0

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.

astralmaster
  • 2,344
  • 11
  • 50
  • 84
  • SQL queries do not have dynamic column names. If you want to have dynamic names, you need to use dynamic SQL. – Gordon Linoff Sep 24 '17 at 14:33
  • Possible duplicate of [Efficiently convert rows to columns in sql server](https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) – Alberto Martinez Sep 26 '17 at 11:34
  • @AlbertoMartinez That question is looking for alternatives to PIVOT. My question is looking for a solution in general. – astralmaster Sep 26 '17 at 14:21

1 Answers1

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
Serkan Arslan
  • 13,158
  • 4
  • 29
  • 44