Pivot tables in SQL Server. A simple sample.

 

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

Published Tuesday, March 03, 2009 2:49 PM by maspeng

Common tips and tricks from a SQL Developer Support perspective : Pivot tables in SQL Server. A simple sample.