#unpivot

[info-comp]

В данном видео подробно рассмотрены операторы PIVOT и UNPIVOT языка T-SQL.

[indiandotnet]

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

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…

View On WordPress

[ilprogrammatorepigro]

Trasformare i risultati di una query in coppia chiave/valore

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))

Chiave Valore Foo 100 Foo 200 Foo 300 Foo 400 Foo 500

[jrlittle]

"How can I unpivot or transpose my tabular data so that there's only one record per row?" I see this question a lot and I thought it was worth a quick Friday blog post. Data often aren’t quite in the format that you want. We usually provide CSV / XLS access to our data in “pivoted” or “normalized” form so they look like this: Pivoted Data But for a lot analyses and applications, particularly data visualisation tools like D3, ggplot2, Tableau, it’s more convenient to have your data “unpivoted” or “denormalized” so it looks like this: Unpivoted Data Although this is less space efficient, space is cheap, and it means there’s always only one record per row, so you can use simple functions to access and filter data. Here are three ways to “unpivot” or “denormalize” your data - in effect, to transpose columns to rows and have one complete record per row.

[errorbusy]

[dataprose]

Undaunted By UnPivot II

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 –…

View On WordPress

[dkvalz]

Standard T-SQL UNPIVOT

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…

View Post

[tsworman]

Unpivot vs separate queries.

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.

Other neatness: When hinting, someone pointed out that I can alias both the unpivot and the table. I was trying parallel hints on the unpivot alias of 'a' it was just ignoring them. Adding a table alias of x and hinting on that works nicely though.