Pivot tables in SQL Server. A simple sample.

Pivot tables in SQL Server. A simple sample.

Rate This
  • Comments 36

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

Leave a Comment
  • Please add 8 and 1 and type the answer here:
  • Post
  • Thank you for submitting this cool story - Trackback from DotNetShoutout

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

  • what is mean of FOR  keyword in Pivot table?

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

  • thank you for easy example and very useful

  • 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.aspx

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

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

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

  • Thanks for your explanation. It's very useful.

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

  • 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

  • Thanks for this simple, well documented example, bro.

  • 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

  • Thank you for the post. Exactly to the point.

Page 1 of 3 (36 items) 123