I’m working with a dataset that has product information stored as key-value pairs, and I need to pivot this data into columns. The problem I’m facing is that some of my column identifiers have square brackets in them, which is causing issues with my pivot query.
Here’s what my data looks like:
| ProductCode |
ProductName |
| 100 |
Widget |
| 200 |
Gadget |
| 300 [kg] |
Heavy Item |
When I try to pivot this table, the row with brackets breaks my query. I’ve tried a few different approaches but can’t seem to get it working properly. Has anyone dealt with this before? What’s the best way to handle column names that contain special characters like square brackets in a pivot operation?
Hmm, interesting - are you using dynamic or static pivot? I’ve seen this break in different ways depending on which approach you take. What’s the exact error message? Also, what’s your data source? Coming from an ERP system where you can’t control the naming convention?
Yeah, this is annoying. Easiest fix is replacing the brackets before you pivot - use REPLACE to swap them for underscores or dashes. Then pivot normally. Way simpler than messing with quotename stuff.
I ran into this exact problem with an inventory reporting project. You need to wrap column names with brackets in additional square brackets to escape them properly in SQL Server. So 300 [kg] becomes [300 [kg]] in your pivot query. The outer brackets tell SQL Server it’s one identifier. If you’re generating column names dynamically, use QUOTENAME() - it handles the escaping automatically. This works across different SQL Server versions when dealing with product codes that have units or other bracketed stuff.