Pivot in sql server 2008 Part 54 Video Lecture - SQL Server Administration

hello welcome to presume technologies I
am Venky this is part 54 of sequel
server one of my YouTube channel
subscribers has asked me to make a video
on pivot operator so in response to his
request here we are with another sequel
server video on pivot operator before
continuing with the session I strongly
recommend to watch parts 11 and 48 of
this video series where we have
discussed about blue bye and derived
tables so what's this pivot operator
pivot is a sequel server operator that
can be used to turn unique values from
one column into multiple columns in the
output they by effectively rotating a
table let's understand what we mean by
this definition with an example I have
this table TBL product sale which has
got three columns sales agent sales
country and sales amount if you look at
the data we have three sales agents John
Tom and hibbett selling in three
different countries US UK and India we
also have the sales amount in the same
table now let's say we want to write a
query which produces the total sales
amount by sales country and by sales
agent so obviously if we have to achieve
this we can make use of crew by we have
spoken about grew by in a great detail
in part 11 of this video series so if
you are new to glue by I strongly
suggest to watch that video first so in
the output we want sales country sales
agent and total sales amount so in the
Select clause sales country sales agent
sum of sales amount as total so we get
all the three columns from TBL product
sales table group first by sales country
and then by sales agent order by in the
same order sales country first and then
sales agent so this query would produce
this output for us okay now I would say
this data can be better presented in a
cross tab format now let's understand
what we mean by crosstab format
so this is the same data which our group
by query has output produced now if you
look at this David has made a total
sales of 960
in India and if you look at the way the
same data is presented here this is in a
crosstab format so David made a total
sales of 960 in India along the same
lines Tom made a total sales of 30 and
40 in UK so Tom Tom UK thirteen forty so
it's the same data except that we are
presently think this in a crosstab
format and to achieve this we are making
use of the pivot operator so let's see
what's happening here now if you look at
this this India US UK these are actually
column values for sales country column
but then we are converting these column
values into column names in the output
and if you look at the definition of the
pivot operator pivot is a sequel server
operator that can be used to turn unique
values from one column so unique values
from one column sales country column
into multiple columns in the output so
if you look at this we are taking the
unique values of the sales country
column India US UK and turning them into
column names in the output and that's
what pivot operated us and which gives
us the effect of rotating a table okay
so let's now see how to actually write
this pivot operator query so if you look
at this in the output we want sales
agent India US and UK columns so select
sales agent India US UK from TBL
products sales
so this is our TBL products sales table
in the TBL products sales table we only
have got sales agent column we don't
have India US and UK so where are these
columns coming from these columns are
the pivoted columns produced by the
pivot
operator so if you look at this pivot
operator that we have what this pivot
operator doing its basically performing
a sum aggregate function on the sales
amount column because we want the total
sales amount and then in the output we
want this India US UK to be pivoted so
for and these columns in the US UK they
are the column values of sales country
column so for sales country column in
India US UK so these are the values that
we want to treat as columns so now this
pivot operator produces them as columns
which are then selected by the Select
out auto select query and we are giving
this pivot whatever this pivot operator
produces an earliest pivot table so it's
as simple as that
select non pivoted columns from the
actual table or it can be a table
expression as well we will talk about
table expressions in the next slide and
then the pivoted columns from the pivot
table and within the pivot operator you
specify an aggregate function and the
column on it you're performing the
aggregate function and for the values
which in turn will be converted into
pivoted columns for the outer select
query so it's as simple as that
let's look at this in action so this is
how product sales table which has got
sales agent sales country sales amount
and this is our group by query
performing a group buy on sales country
and sales agent so if we execute that we
should get total sales by sales country
and sales agent next if we want to
present that in a crosstab format we can
make use of pivot operator which will
convert the unique values in the sales
country column into column names you
know India UK us by making use of this
pivot operator so if you look at the
pivot operator we are performing this
aggregate function on the sales amount
column for sales country column for
these values India US UK which in turn
will be converted into pivoted columns
so let's execute this query and we
should get the output that we expect
okay now let's get a slight variation of
this okay now I have a table here which
is called TBL products sale so if you
look at this table it's pretty much
similar to the table that we have seen
just now sales agent sales country sales
amount in addition to these three
columns we have the ID column as well
okay and look at the pivot query again
this pivot query is the same query now
if you look at this query know obviously
the pivot operator is giving the sum of
sales amount for sales country in India
US UK these values will be converted
into pivoted columns so India US UK from
this pivot table where a sales agent
from TBL products here now apart from
sales agent and sales country and sales
a month we also have the ID column
because of the presence of this ID
column in this original table okay look
at what's going to happen when I execute
this query look at the output we didn't
expect this output now I got 20 rows
back
okay John Tom David India US UK now
these users are basically repeated and
if you look at the number of rules that
we have in TBL products sales we have 20
rows there but my pivot query is also
giving me 20 rows and this output is not
what I have expected and that's because
of the presence of the ID column in TBL
product sales and you are directly
retrieving from TBL product sales so
it's also taking the ID column into
consideration and we have this output
that we haven't anticipated so obviously
to correct this you know it's pretty
simple all you do is you use a derived
table and then select only the columns
that you need so what we are doing here
first
look at this select only sales agent
sales country and sales amount from TBL
products here so obviously if I execute
this query I get all the rows without
the ID column so it's as if we have only
three columns it's
it's like the same table that we have
before and then you are giving this a
name there's nothing but a derived table
and we have spoken about derived tables
as well so you are giving that a derived
table source table as the name for that
derived table and then the rest of the
query is pretty much similar to what we
have already seen so we have pivot sum
of sales amount for sales country for
these columns I mean for these column
values India US UK and you are giving it
an earliest pivot table and you are
selecting sales agent not directly from
TBL product sales but from the source
table it derived the table and then
India US UK these are coming from the
pivot table because those are the
pivoted columns this is the non pivoted
column which is coming from source table
so now when we execute this query we get
the output that we expect the same as
before so if you have other columns in
the table apart from just the columns
that you are operating on look at this
if you look at the you know this pivot
query or this pivot query we are only
talking about sales agencies country and
sales amount columns but we also have
additional column here ID and if you you
know don't want those columns to be
involved in the output then make use of
the derived tables which exclude those
columns and then in that derived table
we only select the columns that we want
to include in the pivot and we get the
output that we expect otherwise you end
up an output like this which you see
here which is not quite what we wanted
all right so if you look at you know the
pivot query here I mean it seems a
little confusing but actually not it's
pretty simple if you understand the
syntax of the pivot query so from MSDN
this is the syntax of the pivot operator
so if you look at this select non
pivoted column and then comma first
pivoted column you can give it an alley
as if you want we haven't given the
alias so first pivot column second
pivoted column and how many ever pivoted
columns you have
from your select query that produces you
know the derived table because you know
keep in mind in the actual source table
if you have more columns than what you
want in your output then the output may
not be what you expect
so in those circumstances make use of
this table expression or derived table
so select the query that produces the
data and you can give it an earlier and
then so that acts as the source for your
select query from where you will get the
non pivoted columns but for pivoted
columns you actually get them from the
pivot operator okay
so a pivot operator will have an
aggregate function and that aggregate
function obviously performs aggregation
on some column in our example it was
doing it on sales amount column for
column that contains the values that
become the column headers in our case
sales country column in first pivot
column comma second pivot column you
know you can relate this syntax directly
with this query that we have and another
thing to keep in mind you can also have
optional order by clause on this slide
you can find resources for asp.net
c-sharp and sequel server interview
questions that's it for today thank you
for listening have a great day