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;

asked 13 Nov, 11:06

Rolle's gravatar image

Rolle
517364353
accept rate: 0%

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

(13 Nov, 12:30) Volker Barth

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

(13 Nov, 12:39) Rolle

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?

(14 Nov, 15:26) Rolle
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×11

question asked: 13 Nov, 11:06

question was seen: 69 times

last updated: 14 Nov, 15:26