Pivot tables in SQL Server. A simple sample.
The other day I was wondering about how to use Pivot tables in SQL Server with SQL, and I didn’t find any simple examples on this.
So I had to do my own and I thought I’d share this here and also as to have as a future reference for myself.
So let’s start with a fictional scenario.
In this case we have lots of vendors who report in their daily income to us, for this we have a simple table that looks like this.
create table DailyIncome(VendorId nvarchar(10), IncomeDay nvarchar(10), IncomeAmount int)
--drop table DailyIncome
Nothing odd here, just the Vendor id, the day of the week they are referring to and what the income on that day was.
So let’s fill it with some data.
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
A lot of data that it is hard to make something useful of, for example, say that we would like to know what the average income is for each vendor id?
Or what the maximum income is for each day for a particular vendor? Enter the pivot table.
To find the average for each vendor, run this query:
select * from DailyIncome
pivot (avg (IncomeAmount) for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) as AvgIncomePerDay
Outcome:
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
The find the max income for each day for vendor SPIKE, run this query:
select * from DailyIncome
pivot (max (IncomeAmount) for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) as MaxIncomePerDay
where VendorId in ('SPIKE')
Outcome:
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
You can of course use this SQL in your C# apps and then bind it to, for example, a datagrid.
static void Main(string[] args)
{
string cs = @"Data Source=<your server>;Initial Catalog=<your database>;Integrated Security=SSPI";
try
{
using (SqlConnection con = new SqlConnection(cs))
{
con.Open();
string sql = "select * from DailyIncome pivot (avg (IncomeAmount) for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) as AvgIncomePerDay";
SqlDataAdapter da = new SqlDataAdapter(sql, con);
DataTable dt = new DataTable("AverageIncomeForVendor");
da.Fill(dt);
// Bind the DataTable to whatever, just displaying it in console here.
int colCount = dt.Columns.Count;
foreach (DataRow row in dt.Rows)
{
StringBuilder sb = new StringBuilder();
for (int i = 0; i < colCount; i++)
{
sb.Append(row[i].ToString() + "\t");
}
Console.WriteLine(sb.ToString());
}
con.Close();
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
}
More info here:
"SQL Server 2008 Books Online (February 2009) - Using PIVOT and UNPIVOT"
https://msdn.microsoft.com/en-us/library/ms177410.aspx
"Pivot table"
https://en.wikipedia.org/wiki/Pivot_table
Comments
Anonymous
March 03, 2009
Thank you for submitting this cool story - Trackback from DotNetShoutoutAnonymous
March 03, 2009
Thank you for submitting this cool story - Trackback from progg.ruAnonymous
April 12, 2011
what is mean of FOR keyword in Pivot table?Anonymous
May 24, 2011
Thank you. i am searching for this type of simple example Thank you very muchAnonymous
June 14, 2011
thank you for easy example and very usefulAnonymous
June 30, 2011
Jasmeet, For is defining where the pivot column values are coming from. There is also a list of values following the column name. Here are a few more samples www.kodyaz.com/.../t-sql-pivot-tables-in-sql-server-tutorial-with-examples.aspxAnonymous
August 02, 2011
Thanks for providing solution But I want fetch data from two different table using pivotAnonymous
November 24, 2011
Calculate percentage of income amount for spike????????Anonymous
February 02, 2012
Ok, if you want a total for day and a total for vendor what is the solution? whit PIVOTAnonymous
May 17, 2012
Thanks for your explanation. It's very useful.Anonymous
May 22, 2012
This article is very good for getting the basic concept of pivot in sql.Anonymous
July 13, 2012
They way you've broken it down and kept it simple is so helpful. I wish others that explain would do as you do. 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 vendorAnonymous
October 31, 2012
Thanks for this simple, well documented example, bro.Anonymous
December 18, 2012
Thank you for your post. i need one more help. please help me out. instead of income day i have date in tat column. my table is like this create table EmpAttendance ( Eid int, EmpName varchar(100), Designation varchar(50), Status varchar(10), Sdate date, Autoid int identity(1,1) ); Eid EmpName Designation Status Sdate Autoid 1 abc Trainee P 2012-12-19 1 2 def trainee P 2012-12-19 2 i want output as EmpName 2012-12-19......... the date continues lik this 2012-12-26 abc P ...... ..... P def P AAnonymous
February 11, 2013
Thank you for the post. Exactly to the point.Anonymous
February 22, 2013
Check this link out for PIVOT and UNPIVOT Operators www.aboutsql.in/.../pivot-and-unpivot-operators-in-t-sql.htmlAnonymous
March 14, 2013
Been searching for hours trying to find a simple solution. I couldn't get anything else to work but using your example my query worked on the first try. So glad I finally found this!Anonymous
March 14, 2013
Very Very nice explaination and easy to understand. Thanks a lot....Anonymous
May 17, 2013
check this link sandipgsql.blogspot.in/.../pivot-unpivot-data-in-sql-server-pivot.htmlAnonymous
May 18, 2013
Check this link for pivot unpivot data a good example sandipgsql.blogspot.in/.../column-data-in-comma-seprated-string.htmlAnonymous
June 03, 2013
I've spent more than an hour looking for a clean, straight to the point solution before finding this. Three years later, we are all still appreciating your excellent discussion.Anonymous
June 25, 2013
what if we want to calculate total incomeamount for each vendoridAnonymous
July 03, 2013
Hi, can someone please show me how to calculate a running total when using a pivot table?Anonymous
September 11, 2013
I dont want to use aggregate funcyion with it. So i just removed the aggregat function. When i removed that aggregate function, It is showing error near to for statement. Also, When i have the column with ImagaData datatype, The puivot table is not working.. Please assist me.Anonymous
October 30, 2013
I have a database like PROD_ID, SALE_DATE, SALE_AMOUNT 1, 01-Mar-2011, 20.00 1, 05-Feb-2011, 32.00 2, 09-JUN-2011, 35.99 2, 02-Apr-2011, 12.00 1, 04-May-2011, 13.00 need a TSQL to get the total sale of each product in each month . Output should be like PROD JAN FEB MAR APR MAY JUN JULY AUG SEP OCT --------- DEC 1 NULL NULL 20.00 NULL NULL NULL NULL 2 NULL NULL NULL 35.99 NULL NULL 12.00 --------------------- CAN you give me the code pleaseAnonymous
November 18, 2013
Thank you, for this simple, but very explanatory example. Very, very useful!!!Anonymous
December 04, 2013
Thank you so much! Have been struggling with pivot for some time.. this is the best to get the basics. :-)Anonymous
December 21, 2013
Thanks a lot for your explanation of pivot table.Anonymous
February 23, 2014
Thanks for giving us a nice example now i understandAnonymous
February 28, 2014
The problem with your example and many like it is you KNOW how many columns So you hard code MON TUE...and so on. that is rarely a real life exampleAnonymous
March 04, 2014
Dude, thanks so much. Had never used pivot before and this was an awesome tutorial on it in SQL.Anonymous
April 07, 2014
The comment has been removedAnonymous
April 23, 2014
thank u for giving such a beautiful example..........Anonymous
May 22, 2014
Well explained...thank you for that!Anonymous
June 22, 2014
What is pivot definition with example?Anonymous
July 16, 2014
if this type of table record VendorId MON TUE WED THU FRI SAT SUN
SPIKE 900 200 500 300 300 100 400 then how we convert this into( VendorId Month 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 this type
Anonymous
July 28, 2014
Thank you its really very much useful in understanding the concept of pivot element .Anonymous
August 06, 2014
where to bind the datatable ? please help me .Anonymous
August 12, 2014
Thank you very much for this excellent explanation! It gives a good point to start from exploring this command further.Anonymous
September 17, 2014
Thanks a lot. Was searching for this kind of thing for some time and finally this popped up :DAnonymous
December 03, 2014
Thank you, simple is best. I wish more folks would provide such eloquent examples so we could all stop searching and start working.Anonymous
January 27, 2015
Thank you for the Submitting i had very useful.Anonymous
February 20, 2015
Great posting. It really helps to understand a somewhat difficult concept. ThanksAnonymous
November 29, 2015
I would like to know how we can aggregate multiple under the same date. Like under the particular day I want to see Sold Quantity and Sold Value for any particular products. Hope I have created my point. SUNDAY MONDAY Feeds 5 1,234 6 2,456 Johns 3 879 0 0 Spike 6 2329 2 1,950 Kindly help