3

I am fully familiar with the following method in the link for performing a dynamic pivot query. Is there an alternative method to perform a dynamic pivot without storing the Query as a String and inserting a column string inside it?

http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx

Improve this question

1 Answer 1

Reset to default
2

Short answer: no.

Long answer:

Well, that's still no. But I will try to explain why. As of today, when you run the query, the DB engine demands to be aware of the result set structure (number of columns, column names, data types, etc) that the query will return. Therefore, you have to define the structure of the result set when you ask data from DB. Think about it: have you ever ran a query where you would not know the result set structure beforehand?

That also applies even when you do select *, which is just a sugar syntax. At the end, the returning structure is "all columns in such table(s)".

By assembling a string, you dynamically generate the structure that you desire, before asking for the result set. That's why it works.

Finally, you should be aware that assembling the string dynamically can theoretically and potentially (although not probable) get you a result set with infinite columns. Of course, that's not possible and it will fail, but I'm sure you understood the implications.

Update

I found this, which reinforces the reasons why it does not work.

Here:

SSIS relies on knowing the metadata of the dataflow in advance and a dynamic pivot (which is what you are after) is not compatible with that.

I'll keep looking and adding here.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I agree with the short answer, but I'm not sure about the long answer.
@Conrad How would you put that?
Honestly I don't know why the SQL Server team went with a syntax that doesn't allow for dynamic columns so I can't say. I do know that the output of Select * from sometable in a proc or just as a query will indeed change if the structure of sometable changes so it can't be because "the DB engine has to be aware of the result set structure".
select * is just a sugar syntax. At the end, the returning structure is "all columns in such table". And you know what they are. The other thing is having dynamic columns defined by whatever data. However, I'm slightly changing my answer, as I could not find sources to back up my answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.