I have a table of key-value pairs that I want to pivot.
Unfortunately, some of the records contain square brackets. How can I transform or adopt my SQL statement to work with square brackets?
Thanks!
| ItemID | ItemValue |
|---|---|
| 1 | A |
| 2 | B |
| 3 [mm] | C |
2
Two ways. You escape a closing square bracket by doubling it up,
select
*
from(values
('1' ,'A')
,('2' ,'B')
,('3 [mm]','C')
)v(ItemID,ItemValue)
pivot(
max(ItemValue)
for ItemID in ([1],[2],[3 [mm]]])
)p
or don’t use square brackets as delimiters but double quotes instead (many hate this).
select
*
from(values
('1' ,'A')
,('2' ,'B')
,('3 [mm]','C')
)v(ItemID,ItemValue)
pivot(
max(ItemValue)
for ItemID in ("1","2","3 [mm]")
)p