UNPIVOT in SQL Server Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 119 of sequel server
tutorial in this video we'll discuss the
unfavored operator in sequel server
pivot operator turns rows into columns
whereas the unfavored operator turns
columns into rows we discussed pivot
operator in detail and part 53 of this
video series in this video we'll discuss
the unpaired operator let's understand
the Sun pivot operator with an example
so we have a table on the left here we
have two sales agents David and John
selling in three different countries
India US and UK at the intersection of
the sales agent name and the country
name we have the sales amount made by
that sales agent in that respective
country so we want to represent this
data on the left like the data on the
right that you see here so essentially
what we are doing here is turning these
three columns India US and UK these
three country columns into rows in our
result set and how could we do that the
easiest way to achieve this is by using
the unpaired operator
what is unpaired operator do it will
turn columns that is these three columns
India US and UK in two rows within our
result set so notice here within the
table on the Left David made a sale of
960 within India and the way we want to
represent that in our result set is we
have this health agent name we have the
country name and then we have the sales
amount he made so for David there are
three rows in the US UK
similarly for John there are three rows
now India US UK so let's see how to
achieve this using the unpaired operator
now what are the columns that we want in
our result set we want sales agent
country and sales amount so sales agent
country and sales amount now the
interesting thing to keep in mind here
is that within this TBL product sales
table so this is the table now do we
have country and sales amount columns no
we don't all the columns that we have
here is we have sales agent and the
other columns are India US UK we don't
have country and say
Samad columns well where are we going to
get them from then we are going to get
them from the unpaid operator so the Sun
pivot operator is going to generate
those columns for us so I'm going to use
the unfavored operator now what do we
want to retrieve we want to retrieve the
sales amount that we see at the
intersection of the sales agent name and
country name so sales amount for now we
want to unpick up the country column
right so I'm going to call this country
okay each country we want to unprovided
so I'm going to call it country in what
are the three country columns that we
have India US UK so for country in India
US UK and let's give this an early as
let's call this on pivot example okay
all right so let's execute this and see
what we get look at that now we get the
result that we expect you know that we
see on the slide right here now if you
look at these two columns country and
sales amount where are they coming from
they're coming from the unpaid operator
so whatever we specified here sales
amount that is going to be one column
within our result set and country is
going to be another column now the does
these name have to be sales amount and
country not necessarily you can give
them any names you want for example I
can say sales amount as ABC but the
moment I do that you know we will not
have sales amount available for the
select list here so you want to call it
ABC instead of sales amount and
similarly I can change this to maybe XYZ
if I do that then I have to change the
name within the select list as well so
this is going to be XYZ now when we
execute this the column name four
countries will be XYZ for the sales
amount it will be ABC so pretty
straightforward and pivot operator
extremely useful if you want to turn
columns into rows thank you for
listening and have a great day