Aug 19, 2014

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);
            }
        }

SELECT
  [non-pivoted column], -- optional
  [additional non-pivoted columns], -- optional
  [first pivoted column],
  [additional pivoted columns]
FROM (
  SELECT query producing sql data for pivot
  -- select pivot columns as dimensions and
  -- value columns as measures from sql tables
) AS TableAlias
PIVOT
(
  <aggregation function>(column for aggregation or measure column) -- MIN,MAX,SUM,etc
  FOR [<column name containing values for pivot table columns>]
  IN (
    [first pivoted column], ..., [last pivoted column]
  )
) AS PivotTableAlias
ORDER BY clause -- optional

--- pivot column to row
SELECT SchemeID,[Set],[Value]
FROM dbo.i3s_Scheme_Group
UNPIVOT (Value FOR [Set] IN  (SET_1,SET_2,SET_3,SET_4,SET_5,SET_6,SET_7,SET_8,SET_9,SET_10)
)AS SchemeSet WHERE [Value]=1 ORDER BY SchemeID

0 comments:

Post a Comment

Nam Le © 2014 - Designed by Templateism.com, Distributed By Templatelib