Transform rows into columns MS SQL

Question

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.

SQL queries do not have dynamic column names. If you want to have dynamic names, you need to use dynamic SQL. — Gordon Linoff
– Gordon Linoff, Commented Sep 24, 2017 at 14:33
Possible duplicate of Efficiently convert rows to columns in sql server — Alberto Martinez
– Alberto Martinez, Commented Sep 26, 2017 at 11:34
@AlbertoMartinez That question is looking for alternatives to PIVOT. My question is looking for a solution in general. — astralmaster
– astralmaster, Commented Sep 26, 2017 at 14:21

Serkan Arslan · Accepted Answer · 2017-09-24 16:22:30Z

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

edited Sep 24, 2017 at 16:22

answered Sep 24, 2017 at 15:59

Serkan Arslan

13.4k4 gold badges33 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

astralmaster Over a year ago

I'm sorry, I forgot to mention that the number of rows in tblColumnNames (and therefore columns for the resultant table) will constantly change

astralmaster Over a year ago

Thank you, so basically it was about pivot and dynamically generating column names

Collectives™ on Stack Overflow

Transform rows into columns MS SQL

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related