Reverse PIVOT table in SQL Server Video Lecture - SQL Server Administration

this is part 120 of sequel server
tutorial in this video we'll discuss if
it's always possible to reverse what a
pivot operator has done using the
unfavored operator first let's answer
this question is it always possible to
reverse what a pivot operator has done
using unfavored operator no not always
if the pivot operator has not aggregated
the data then you can get your original
data back using the unfavored operator
but not if the data is already
aggregated let's actually prove this
with examples on the left we have a
table notice we have got two different
sales aliens selling in two different
countries David and John selling in
India and us notice here within this
table we have a row for every country
now what we want to do is convert these
country rows into columns as you can see
in the results at right here the easiest
way to do that is by using pivot
operator so let's convert these country
rows into columns we have that same
table right here TBL products sales so
what do we want within our result set we
want sales agent a column for India and
a column for us so the Select list is
going to contain those columns sales
agent column for India and the column
for us now do we already have those two
columns in TBL products sales table no
we don't where are those columns going
to come from they're going to come from
the pivot operator so I'm going to apply
the pivot operator with the pivot
operator we specify an aggregate
function so I'm going to use the sum
aggregate function so we want to
aggregate the sales amount column okay
and what is the column that we want to
pivot we want to pivot the country
column so for country n so what are the
column values that we want to pivot
India and us the column is country and
within that we have India and us those
are the values that we want to pivot so
for country in India US and let's give
this an alias let's call this pivot
example
all right so now when we execute this
notice that we get the pivoted data
okay so pivot operator converting rows
into columns now on this if we apply the
unpaid or Preta will I be able to get my
original data back so on this result set
if we apply the unpaid operator we'll be
able to get this original data back the
answer is yes in this case because we
look at this some aggregate function
that the spirit operator is using it's
not actually aggregating any data so
whatever data we have here we have the
same data right here so david india 960
david india 960 david u.s. 520 so 520
and john india 970 and john u.s. 540 so
data is not aggregated so if we apply
the unfavored operator along with this
pivot operator we should be able to get
our original data back let's actually
prove that with an example so now I'm
actually going to make a copy of this so
I'm going to give this an Ali ass so
let's call this pay and on this I'm
going to spell if specify a select
clause so when we unpeopled this we
should get this original data back that
means these are the columns that are
going to be present in our original
result set so sales agent country and
sales amount so that I'm those three
columns I am going to specify within our
select list sales agent country and
sales amount from so here is the pivot
result set on this I'm going to apply
the unpaid operator so unpaid what do we
want to unfavored sales amount for
country right and what are the two
different
columns that we want to column values
that we want to unpaired India and us so
for country in India and us so this
pivot operator has pivoted country
column and those are the values and this
unfit operator is going to unfit country
column and those are the values so we
should be able to get our original data
back and let's give this analyst let's
call this ass and pivot example all
right so let's execute this query and
see if we will get our original data
back look at that now I get the original
data back so in this case we are able to
get our original data back because
there's some aggregate function that we
have used with the pivot operator it did
not perform any aggregation now let's
look at an example of when we'll not be
able to get the original data back when
we use the unpaired operator now at the
moment if you look at this table TBL
product sales we don't have any
duplicate entry you know when we take
sales agent in country now what I'm
going to do is introduce a duplicate row
so here we have another entry for David
and India so let's execute this and
let's execute our select statement now
so if you look at sales agent David and
country India we have two entries one
entry has a sale amount of 960 and
another entry had a sale amount of 100
as you can see here so now when we
execute our pivot query that we have
written before what it's going to do is
aggregate the sales amounts for India
country so when we add 100 to 960 what
do we get we get 1060 so David made a
sale of thousand sixty for India okay so
it has aggregated that now when we apply
the unpaired operator on this result set
will be get our original data back let's
actually look at that in action before
that let's execute our pivot query right
here so now when we execute this
look at what we get we get the aggregate
data for David India you know so now if
we apply the unpaired operator let us
see if we get our original data back now
before we do that let's execute this
query from TBL products sales table
notice how many rules we have we have
five rules here right but when I execute
the query that has both pivot and
unfavorite operators look at what I am
going to get I am only getting four rows
let's actually do this let's execute
this Select query and our pivot query
together okay all right
so in the bottom you see the data that's
the original data and this is the result
that we get when apply the pivot and
then the unfavorite operator look at
that for david india i got only one row
okay I didn't get two rows but in our
original results at david india a 960
david india 100 but when I apply pivot
and then when I apply an pivot after
that I only get one row for David and
country India okay so in this case when
the data is aggregated we are not able
to get the original data back when we
apply the unfavored operator why is that
that's because sequel server in this
case has no idea on how to undo that
aggregation that's the reason thank you
for listening and have a great day