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”


http://msdn.microsoft.com/en-us/library/ms177410.aspx


“Pivot table”


http://en.wikipedia.org/wiki/Pivot_table


Comments (44)

  1. Thank you for submitting this cool story – Trackback from DotNetShoutout

  2. progg.ru says:

    Thank you for submitting this cool story – Trackback from progg.ru

  3. jasmeet says:

    what is mean of FOR  keyword in Pivot table?

  4. sathish says:

    Thank you. i am searching for this type of simple example Thank you very much

  5. Prasad MYV says:

    thank you for easy example and very useful

  6. James says:

    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 http://www.kodyaz.com/…/t-sql-pivot-tables-in-sql-server-tutorial-with-examples.aspx

  7. Perwez says:

    Thanks for providing solution But I want fetch data from two different table using pivot

  8. Reema says:

    Calculate percentage of income amount for spike????????

  9. Alfredo says:

    Ok, if you want a total for day and a total for vendor what is the solution? whit PIVOT

  10. Santosh says:

    Thanks for your explanation. It's very useful.

  11. Varun Sareen says:

    This article is very good for getting the basic concept of pivot in sql.

  12. Erik says:

    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 vendor

  13. WiseMan says:

    Thanks for this simple, well documented example, bro.

  14. Shallet says:

    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                                                 A

  15. Anaya Upadhyay says:

    Thank you for the post. Exactly to the point.

  16. AboutSQL says:

    Check this link out for PIVOT and UNPIVOT Operators

    http://www.aboutsql.in/…/pivot-and-unpivot-operators-in-t-sql.html

  17. KW says:

    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!

  18. Gobind says:

    Very Very nice explaination and easy to understand. Thanks a lot….

  19. Sandip says:

    Check this link for pivot unpivot data a good example

    sandipgsql.blogspot.in/…/column-data-in-comma-seprated-string.html

  20. Tim -Charlotte says:

    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.

  21. hamza says:

    what if we want to calculate total incomeamount for each vendorid

  22. LJ says:

    Hi, can someone please show me how to calculate a running total when using a pivot table?

  23. Bhuvana, says:

    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.

  24. sultana says:

    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 please

  25. rasha says:

    Thank you, for this simple, but very explanatory example.

    Very, very useful!!!

  26. Lilo says:

    Thank you so much! Have been struggling with pivot for some time.. this is the best to get the basics. 🙂

  27. sweet bansal says:

    Thanks a lot for your explanation of pivot table.

  28. Kumar says:

    Thanks for giving us a nice example now i understand

  29. Dave says:

    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 example

  30. JaDaar says:

    Dude, thanks so much.  Had never used pivot before and this was an awesome tutorial on it in SQL.

  31. Me says:

    This is what I wanted. A step-by-step and for-beginners line-per-line explained example.

    Thanks. 🙂

  32. sinivas says:

    thank u for giving such a beautiful example……….

  33. Praveena says:

    Well explained…thank you for that!

  34. Naresh says:

    What is pivot definition with example?

  35. bipin singh says:

    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

  36. Jigish Desai says:

    Thank you its really very much useful in understanding the concept of pivot element .

  37. Michelle says:

    where to bind the datatable ? please help me .

  38. John says:

    Thank you very much for this excellent explanation!

    It gives a good point to start from exploring this command further.

  39. Charitha says:

    Thanks a lot. Was searching for this kind of thing for some time and finally this popped up 😀

  40. bFricker says:

    Thank you, simple is best. I wish more folks would provide such eloquent examples so we could all stop searching and start working.

  41. Brahmareddy says:

    Thank you for the Submitting i had very useful.

  42. Dale says:

    Great posting. It really helps to understand a somewhat difficult concept.

    Thanks

  43. M Asghar SHAH says:

    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