question

Steve-1405 avatar image
0 Votes"
Steve-1405 asked ErlandSommarskog answered

Dynamic pivot query not for calculating

Hi all.
I need to create a dynamic pivot table in my SQL but I will not be calculating in the table, I will just be "stringing out" values in two columns. I have tried many different examples of dynamic pivots but nothing seems to work.

There are multiple accounts and an indefinite number of 'PRV_DEP' records.
And it has to end up in a temp table so I can add it to the end of records in another table.

Any help would be greatly appreciated!
Thanks
Steve

110424-dynamicpivot.jpg


sql-server-transact-sql
dynamicpivot.jpg (130.8 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Could you please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)
along with your expected result? So that we’ll get a right direction and make some test.

0 Votes 0 ·

1 Answer

ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered

I have a primer on dynamic pivot here: https://www.sommarskog.se/dynamic_sql.html#pivot.

And that's all I can say. If you want some to write the dynamic pivot for you, you need to supply CREATE TABLE for your tables and INSERT statements with sample data. We cannot work from a screenshot.

And it has to end up in a temp table


Stop! A dynamic pivot is a non-relational operation. You cannot insert the result of a dynamic pivot into a temp table, since you don't know that temp table is going to look beforehand. Well, you can implement it as a static pivot and assume a max of, say, 30, columns.

But basically, a dynamic pivot must be the last operation you do before you return the data to the client, since you are leaving the relational world. ...or return the data in a relational format, and do the pivoting client-side.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.