#unpivot

How easily you can unpivot the pivot data in SQL Server?

I know when we talk about the pivoting & unpivoting the data then most of the time we are making our faces and we feel it would be a tough task. Trust me after reading this post you feel unpivot is super easy.

Before jumping directly into unpivot want to share pivot link to take a glimpse if you are not aware of it.

Pivot in SQL Server pivot 

Now, let us assume that we have following table of…

In questo brevissimo articolo spiegherò come trasformare i risultati di una qualsiasi query in una coppia chiave/valore, dove la chiave è il nome della colonna e il valore il valore effettivo.

Questa può essere utile nel caso abbia una tabella che abbia valorizzato dei campi con nomi di colonne di un'altra tabella, e abbia la nece3ssità di andare in JOIN su queste.

Prendiamo ora la seguente query, molto semplice:

SELECT Foo FROM Bar

Che fornisce

Foo 100 200 300 400 500

Per trasformare questo risultato in una coppia chiave valore, utilizzo la funzione UNPIVOT (che eventualmente spiegherò in un altro post).

SELECT Key, Value FROM Bar UNPIVOT(Valore FOR Chiave IN (Foo))

Undaunted By UnPivot II

Some time back, Shane Devenshire and I added our two cents to a question a user asked on LinkedIn here. So when I decided to write a post regarding UnPivot using nested For..Next Loops, it only made sense to ask Shane if he would like to write a guest-post and tutorial on his process and Normalization Utility.

So, without further ado, take it away Shane!

Un-pivoting Excel Spreadsheet data –…

In prior blog posts I talked about a Standard PIVOT query and a Dynamic PIVOTquery. It is only fitting that I talk about how to implement an UNPIVOT query. As DBA’s we commonly unpivot data and turn it into normalized data for storing in a relational data…

Our pattern for pulling data into a bridge table involves a cursor which we loop over and insert related data with the appropriate key for that bridge entry. It is readable and fairly easy to debug/tune. I initially populated a bridge table by a rather ugly select inside the standard loop. It duplicated the needed rows through a bunch of unions. The result was it pivoted a table which looks like: 5, x, y, z

into this: 5, x 5, y 5, z It runs in ~5 minutes on 70k rows which seems like a long time. I didn't bother to tune the query at the time. So I'm sure that time can be cut down even more. The indexes are there, but my guess is that it wasn't using them.. Out of curiosity, I rewrote it to use unpivot inside the loop and it was painfully slow (~4-5 hours). The internal query was super elegant so I decided to tweak this version. I noticed the execution plan for the query did a full table scan when performing the unpivot (despite the filters in the where clause, it ignored the indexes for the pivot). I couldn't seem to get it to use the indexes even with hints. Explain plan would return one thing, but Oracle would execute another. Some functionality exists for storing a plan so that it will pick it in the future but that seemed like a poor idea at the time.

I removed the loop and took advantage of the fact it was going to do a full table scan, no matter what. I figured that I might as well return all the data I want at once and I should see a drastic speed reduction as a result. Code like this: select  5 ,a.prov_type, a.prov from    someschema.sometable x   unpivot (prov for prov_type in    (p_id as 'P', v_id as 'V', o_id as 'O', a_id as 'A',  r_id as 'R')) a  Result: ~20 seconds with no hints. I've since added some hints where needed and gotten the results to return even faster. It breaks our standard pattern, but it performs. I think for code review purposes, I'll end up implementing something similar to the select inside a loop. It is nice to know that the functionality exists though.