Skip to main content

PIVOT example in sql server

Explanation :

create table DailyIncome(VendorId nvarchar(10), IncomeDay nvarchar(10), IncomeAmount int)
insert into DailyIncome values ('SPIKE', 'FRI', 100)
insert into DailyIncome values ('SPIKE', 'MON', 300)
insert into DailyIncome values ('FREDS', 'SUN', 400)
insert into DailyIncome values ('SPIKE', 'WED', 500)
insert into DailyIncome values ('SPIKE', 'TUE', 200)
insert into DailyIncome values ('JOHNS', 'WED', 900)
insert into DailyIncome values ('SPIKE', 'FRI', 100)
insert into DailyIncome values ('JOHNS', 'MON', 300)
insert into DailyIncome values ('SPIKE', 'SUN', 400)
insert into DailyIncome values ('JOHNS', 'FRI', 300)
insert into DailyIncome values ('FREDS', 'TUE', 500)
insert into DailyIncome values ('FREDS', 'TUE', 200)
insert into DailyIncome values ('SPIKE', 'MON', 900)
insert into DailyIncome values ('FREDS', 'FRI', 900)
insert into DailyIncome values ('FREDS', 'MON', 500)
insert into DailyIncome values ('JOHNS', 'SUN', 600)
insert into DailyIncome values ('SPIKE', 'FRI', 300)
insert into DailyIncome values ('SPIKE', 'WED', 500)
insert into DailyIncome values ('SPIKE', 'FRI', 300)
insert into DailyIncome values ('JOHNS', 'THU', 800)
insert into DailyIncome values ('JOHNS', 'SAT', 800)
insert into DailyIncome values ('SPIKE', 'TUE', 100)
insert into DailyIncome values ('SPIKE', 'THU', 300)
insert into DailyIncome values ('FREDS', 'WED', 500)
insert into DailyIncome values ('SPIKE', 'SAT', 100)
insert into DailyIncome values ('FREDS', 'SAT', 500)
insert into DailyIncome values ('FREDS', 'THU', 800)

insert into DailyIncome values ('JOHNS', 'TUE', 600)


Now, if we select out the flat data that we have, we will get the following:

VendorId   IncomeDay  IncomeAmount
---------- ---------- ------------
SPIKE      FRI        100
SPIKE      MON        300
FREDS      SUN        400
SPIKE      WED        500
SPIKE      TUE        200
JOHNS      WED        900
SPIKE      FRI        100
JOHNS      MON        300
SPIKE      SUN        400
...
SPIKE      WED        500
FREDS      THU        800
JOHNS      TUE        600

select * from DailyIncome
pivot (avg (IncomeAmount) for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) as AvgIncomePerDay

VendorId   MON         TUE         WED         THU         FRI         SAT         SUN
---------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
FREDS      500         350         500         800         900         500         400
JOHNS      300         600         900         800         300         800         600
SPIKE      600         150         500         300         200         100         400


select * from DailyIncome
pivot (max (IncomeAmount) for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) as MaxIncomePerDay
where VendorId in ('SPIKE')

VendorId   MON         TUE         WED         THU         FRI         SAT         SUN
---------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
SPIKE      900         200         500         300         300         100         400

The short story on how it works using the last query.

select * from DailyIncome                                 -- Colums to pivot
pivot (
   max (IncomeAmount)                                                    -- Pivot on this column
   for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN]))         -- Make colum where IncomeDay is in one of these.
   as MaxIncomePerDay                                                     -- Pivot table alias
where VendorId in ('SPIKE')                               -- Select only for this vendor



Comments

Post a Comment

Popular posts from this blog

show image in asp.net form from database

In order to show image from database to webform first add an image control in webpage.   <asp:Image ID="imgStudentImage" runat="server" Height="157px" Width="160px" /> Then add new webform to read image from database for example displayimage.aspx and in code behind model write the following code. public string str = //your string     public SqlConnection conn;     public SqlCommand sql = new SqlCommand();     public SqlDataReader dr;     public string strSQL;     protected void Page_Load(object sender, EventArgs e)     {         conn = new SqlConnection(str);         conn.Open();         long id = Convert.ToInt64(Request.QueryString["mId"]);         strSQL = "";         strSQL = strSQL + " SELECT snap from S...
Post a Comment
Read more

Export Doc,Access,Image,CSV,Excel,Pdf,XML,HTML,Text,Print of Gridview in Asp.net

First import itextsharp.dll in the solution and use three namespces (basically for pdf) using iTextSharp.text; using iTextSharp.text.pdf; using iTextSharp.text.html.simpleparser;          Then Use the following code :- on each button  click  protected void Page_Load(object sender, EventArgs e)         {             if (!Page.IsPostBack)             {                 BindGridDetails(GridView1);             }         }                 protected DataTable BindGridDetails(GridView GridView1)         {   ...
1 comment
Read more

im2col function in MATLAB explanation

Let us suppose we have A=[1 1;2 2] 1 1 2 2 ... ok Now img2col syntax  == im2col(A,[m n],block_type) Where block type = ('distinct','sliding') distinct places 0's in the final output,,,for padding, sliding,,repeats from the very first matrix element. after all elements read out. Now.. Now [m n] -- very important .... Number of Rows to show = M*N (if m=2 ,, n=2 then each column has 4 elements..) and Number of element fetch to repeat is M in row... That means,, OUTPUT OF THIS - im2col(A,[2 2], 'sliding') ans =      1      2      1      2 -- if  A=[1 1 1;2 2 2] A =      1     1     1      2     2     2 then im2col(A,[2 2], 'sliding') ans = contains 4 rows....as 2*2 ...   and fetch 2 elements as M=2 here... and slidin...
Post a Comment
Read more