I have a problem creating a dynamic pivot table in SQL Server 2012.
My main table consists of timestamp,node_id,value columns:
| timestamp | node_id | value |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 2 | 2 |
I have another table t_node with nodename (NVARCHAR(400)) for each node_id.
I want to have a pivot table with
| timestamp | nodename1 | nodename2 | nodename3 |
|---|---|---|---|
| 1 | 123 | 1 | 123 |
| 2 | 456 | 2 | 455 |
and so on.
I created a dynamic list first with all nodenames like HERE:
DECLARE @cols VARCHAR(MAX)
SELECT @cols = isnull(@cols + ',', '') + '[' + nodename + ']'
FROM [MyDB].[DBSpace01].t_node
ORDER BY nodename
When I print this @cols, I always get trailing whitespaces at the end of each entry – like this:
[nodename1 ], [nodename2 ], [nodename3 ]
This then leads to errors when pivoting with this list because it results in an error:
‘nodename1.’ is an invalid name because it contains a NULL character or an invalid Unicode character.
I tried RTRIM and REPLACE and everything, but the list is either empty or has the whitespaces in the end. I also tried to have it with QUOTENAMES, but then the list is empty.
I do not understand enough of SQL Server to understand what exactly is going wrong here.
Any help is appreciated.
As in the comments requested here are some results of
SELECT CAST(nodename AS VARBINARY(MAX)) FROM [MyDB].[DBSpace01].t_node
0x41004900530049004E0054002F004D004D00530049004C006900730074002F0030000000
0x41004900530049004E0054002F004D004D00530049004C006900730074002F0031000000
5