An Alternative (Better?) Method to UNPIVOT (SQL Spackle)

Question

An Alternative (Better?) Method to UNPIVOT (SQL Spackle)

Viewing 15 posts - 31 through 45 (of 46 total)

You must be logged in to reply to this topic. Login to reply

amd.repetto Ten Centuries Points: 1316 More actions · Answer 1

Dwain,

Thanks for the quick answer! Don't worry, answer me when you have enough time, like I said I'm new on this things and I want to learn properly how the things work 🙂 I'll be expecting anything that you can tell me about my question.

Have a good fly!

Regards,

Mauricio Repetto

A. Mauricio Repetto
ML Engineer

amd.repetto Ten Centuries Points: 1316 More actions · Answer 2

Sorry, I post this as a new reply and I don't know how to delete it:

"Dwain,

Thanks for the quick answer! Don't worry, answer me when you have enough time, like I said I'm new on this things and I want to learn properly how the things work I'll be expecting anything that you can tell me about my question.

Have a good fly!

Regards,

Mauricio Repetto"

But I think I found the answer by myself here http://msdn.microsoft.com/en-us/library/e57cd31d-140e-422f-8178-2761c27b9deb. It seems to be that the 1000 top rows restriction on the values exists only when you use it in a insert statement.

Thanks Dwain for your time.

Regards,

Mauricio

A. Mauricio Repetto
ML Engineer

Dwain Camps SSC Guru Points: 86948 More actions · Answer 3

amd.repetto (4/5/2013)
Sorry, I post this as a new reply and I don't know how to delete it:
"Dwain,
Thanks for the quick answer! Don't worry, answer me when you have enough time, like I said I'm new on this things and I want to learn properly how the things work I'll be expecting anything that you can tell me about my question.
Have a good fly!
Regards,
Mauricio Repetto"
But I think I found the answer by myself here http://msdn.microsoft.com/en-us/library/e57cd31d-140e-422f-8178-2761c27b9deb. It seems to be that the 1000 top rows restriction on the values exists only when you use it in a insert statement.
Thanks Dwain for your time.
Regards,
Mauricio

Here's the same link but you can click on it: http://msdn.microsoft.com/en-us/library/e57cd31d-140e-422f-8178-2761c27b9deb

You can use the ICode Shortcuts on the left to make your posts pretty.

Glad you found the answer to your question. I am also quite happy to hear you're using the CROSS APPLY VALUES approach to UNPIVOT. This 2000 column table sounds like a monster! Is that some sort of data warehouse table?

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

amd.repetto Ten Centuries Points: 1316 More actions · Answer 4

dwain.c (4/7/2013)
amd.repetto (4/5/2013)
Sorry, I post this as a new reply and I don't know how to delete it:
"Dwain,
Thanks for the quick answer! Don't worry, answer me when you have enough time, like I said I'm new on this things and I want to learn properly how the things work I'll be expecting anything that you can tell me about my question.
Have a good fly!
Regards,
Mauricio Repetto"
But I think I found the answer by myself here http://msdn.microsoft.com/en-us/library/e57cd31d-140e-422f-8178-2761c27b9deb. It seems to be that the 1000 top rows restriction on the values exists only when you use it in a insert statement.
Thanks Dwain for your time.
Regards,
Mauricio
Here's the same link but you can click on it: http://msdn.microsoft.com/en-us/library/e57cd31d-140e-422f-8178-2761c27b9deb
You can use the ICode Shortcuts on the left to make your posts pretty.
Glad you found the answer to your question. I am also quite happy to hear you're using the CROSS APPLY VALUES approach to UNPIVOT. This 2000 column table sounds like a monster! Is that some sort of data warehouse table?

Yes, it is a monster! In this case, is not for a dw, my boss wanted data from 5 tables (each one with a lot of columns), and one of the requirements was that the data had to be in an unpivot form.

The data will be send later as json with a format like ",<id>,<name_of_column>,<value>"

Your code save my life!!

Thanks for your time Dwain

A. Mauricio Repetto
ML Engineer

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 5

amd.repetto (4/7/2013)

dwain.c (4/7/2013)

amd.repetto (4/5/2013)
The data will be send later as json with a format like ",<id>,<name_of_column>,<value>"

As a sidebar, that format is usually referred to as an "EAV", which stands for "Entity, Attribute, Value".

If the destination for this data was a table, I'd suggest using the SQL_Variant datatype for the "Value" column to preserve the datatype of the original data (provided there were no datypes, like blobs, that won't work in a variant).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

amd.repetto Ten Centuries Points: 1316 More actions · Answer 6

Jeff Moden (4/7/2013)

amd.repetto (4/7/2013)

dwain.c (4/7/2013)
amd.repetto (4/5/2013)
The data will be send later as json with a format like ",<id>,<name_of_column>,<value>"
As a sidebar, that format is usually referred to as an "EAV", which stands for "Entity, Attribute, Value".
If the destination for this data was a table, I'd suggest using the SQL_Variant datatype for the "Value" column to preserve the datatype of the original data (provided there were no datypes, like blobs, that won't work in a variant).

Yes, you're right it is an "EAV"! Thanks for the advice Jeff, I'm reading now about the sql_variant, I've never heard about it.

A. Mauricio Repetto
ML Engineer

Dwain Camps SSC Guru Points: 86948 More actions · Answer 7

Jeff Moden (4/7/2013)

amd.repetto (4/7/2013)

dwain.c (4/7/2013)
amd.repetto (4/5/2013)
The data will be send later as json with a format like ",<id>,<name_of_column>,<value>"
As a sidebar, that format is usually referred to as an "EAV", which stands for "Entity, Attribute, Value".
If the destination for this data was a table, I'd suggest using the SQL_Variant datatype for the "Value" column to preserve the datatype of the original data (provided there were no datypes, like blobs, that won't work in a variant).

Jeff- It is nice to know someone has my back while I'm in Papua New Guinea! Thanks.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Stuart Pearson SSChasing Mays Points: 637 More actions · Answer 8

Hi, I just successfully ran this in SQL 2012 using a database at compatibility level 80!!

Dwain Camps SSC Guru Points: 86948 More actions · Answer 9

Stuart Pearson (9/26/2014)
Hi, I just successfully ran this in SQL 2012 using a database at compatibility level 80!!

Interesting. I wouldn't have thought that would be possible.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 10

dwain.c (9/28/2014)
Stuart Pearson (9/26/2014)
Hi, I just successfully ran this in SQL 2012 using a database at compatibility level 80!!
Interesting. I wouldn't have thought that would be possible.

What would be really nice is if you did it from 2014 using a compatibility level of 90. That would mean that I could easily migrate from 2005 to 2014 without having to go through 2012. I'm following the old rule of each version only going two versions back. Anyone have any ideas on 2005 migrations to 2014 that they'd like to share? If the discussion starts to get large on that subject, I'll open a new thread on the subject.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Stuart Pearson SSChasing Mays Points: 637 More actions · Answer 11

Stuart Pearson

SSChasing Mays

Points: 637

September 29, 2014 at 2:23 am

#1748731

My mistake it was a SQL 2008 R2 server using SSMS 2012

taylor_benjamin SSC Veteran Points: 286 More actions · Answer 12

I have been demonstrating how to create high performance Pivot transformations of data using SQL or Linq. Those queries both use a Pivot Table to enhance the performance of the transformation.

Today I am posting the inverse, How to Achieve High Performance Un-Pivot Transformations Using a Pivot Table.

You don't have to rely on your reporting engine to perform your Pivot/Un-Pivot work. You can do it in your database engine, or using Linq for non-SQL data sources. The Linq version works with sets that are IEnumerable<>.

This technique is the fastest un-pivot method I have found in the last 20 years, hands down.

Cheers,

Ben

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

taylor_benjamin (9/29/2014)
I have been demonstrating how to create high performance Pivot transformations of data using SQL or Linq. Those queries both use a Pivot Table to enhance the performance of the transformation.
Today I am posting the inverse, How to Achieve High Performance Un-Pivot Transformations Using a Pivot Table.
You don't have to rely on your reporting engine to perform your Pivot/Un-Pivot work. You can do it in your database engine, or using Linq for non-SQL data sources. The Linq version works with sets that are IEnumerable<>.
This technique is the fastest un-pivot method I have found in the last 20 years, hands down.
Cheers,
Ben

Hi Ben

A lot happens to SQL Server in 20 years, the technique you are describing in your blog may well no longer be the fastest kid on the block. How about plugging your method into Dwain's test harness to see how it compares?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

taylor_benjamin SSC Veteran Points: 286 More actions · Answer 14

Sure...

The last version I tested for performance comparisons was SQL Server 2008 R2. So, realistically, my assertion may be incorrect with the latest version. I doubt it, but the assertion should be challenged as you recommend.

Thanks for keeping me honest.

Ben

Bryant McClellan SSCarpal Tunnel Points: 4397 More actions · Answer 15

Ben,

Your solution using aggregate functions is similar to the use of characteristic functions in SQL Server to achieve pivot and unpivot operations in a single SELECT. This was documented in the book 'Optimizing Transact-SQL: Advanced Programming Techniques'. I ran across this book shortly after it was published and, needing a pivot operator in SQL 7, took a shot. I still have the book and still make use of the code. The resulting code is a little obscure but generally holds its own, performance-wise, with other solutions.

------------
Buy the ticket, take the ride. -- Hunter S. Thompson