cancel
Showing results for 
Search instead for 
Did you mean: 

PIVOT clause dynamically

2,072

I use this example for dynamic number of columns with pivot. But how do I remove quotes around the column names?

begin
  create or replace variable @props varchar(120) array;
  select *
    into #propdata
    from sp_property_history();
  set @props = ( select array_agg( distinct name order by name ) from #propdata );
  select *
    from ( select name, ticks, time_recorded, time_delta, value_delta from #propdata ) mysourcedata
   pivot ( sum( value_delta ) delta for name in @props ) mydata
   order by ticks desc;
end;
VolkerBarth
Contributor
0 Kudos

FWIW, I don't know - but I asked a similar question here... - it's still unanswered...

0 Kudos

Ok, it was a shame there is no answer to that.

0 Kudos

Have you tried sa describe query () to get to know the default names of the pivot columns and then use EXECUTE IMMEDIATE to remove the quotes? It should work in some way, right?

Accepted Solutions (0)

Answers (0)