Adding a prefix to PIVOT columns

Question

I'm PIVOTing a table such that the column names in the final table are based on the "organization name" in the source table. The problem is, the "organization name" is data-driven and could be any set of values; I use dynamic SQL to build up the PIVOT's IN clause. This means that the value could match another column name I have, like the RecordId I'm pivoting against. So I have something like this:

.----------------------------------------------------------------------.
| RecordId | OrganizationFoo | OrganizationBar | OrganizationBaz | ... |
|----------|-----------------|-----------------|-----------------|-----|
| 123      | 182             | 76              | 56              | ... |
| 234      | 846             | 0               | 182             | ... |
| 345      | 46              | 2               | 951             | ... |
...

The problem is, this is all data-driven so it would be fine for an organization name to be RecordId, which would cause an invalid SQL query to be generated because of duplicate columns. Is there a way I can automatically prefix something to every column in the PIVOT output?

If not, the only 3 ways I can think of doing it are:

Select CONCAT('Prefix_', [OrgName]) instead, but this will probably reduce efficiency because [OrgName] is indexed.
Create a duplicate table to the one holding [OrgName], and each time this query is run, clear it and insert the contents of the original table but with [OrgName] having the prefix instead.
Just always store [OrgName] with the prefix in the first place.

Any cleaner/better options?

David דודו Markovitz · Accepted Answer · 2016-12-12T15:26:46.913

The index use seems irrelevant to the concatenation.
The other 2 solutions are awkward.

Demo

create table t (x int,code varchar(100))

insert into t values 
    (1,'X'),(1,'X'),(1,'Y'),(1,'Y'),(1,'Y'),(1,'Z'),(2,'X'),(2,'X'),(2,'Y')

select  *
from    t pivot (count(code) for code in([x],[y],[z])) p

Msg 265, Level 16, State 1, Line 13 The column name "x" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument.
Msg 8156, Level 16, State 1, Line 13 The column 'x' was specified multiple times for 'p'.

select  *
from    (select x,'my_prefix_' + code as code from t) t 
            pivot (count(code) for code in([my_prefix_x],[my_prefix_y],[my_prefix_z])) p

+---+-------------+-------------+-------------+
| x | my_prefix_x | my_prefix_y | my_prefix_z |
+---+-------------+-------------+-------------+
| 1 | 2           | 3           | 1           |
+---+-------------+-------------+-------------+
| 2 | 2           | 1           | 0           |
+---+-------------+-------------+-------------+

Yep, you're right about the performance being the same. Strange, I thought I'd tested the non-concatenated version as being faster. Oh well, I guess concatenation is the cleanest solution. Thanks. — Jez, Dec 12 '16 at 16:26
P.s. there is also the option to give aliases to the fixed columns — David דודו Markovitz, Dec 12 '16 at 16:28

Adding a prefix to PIVOT columns

1 Answers1