Pivot function

You have rows and rows and wish you had more columns.  You have 10 clients, 5 years, and a list of activity/orders/contacts.  Lots of rows.  You would rather have 5 rows and a count column for each of the clients.  That is what the pivot function does.  You could probably write code to do this, which is the same as reinventing the wheel, but why work so hard.

The following is annotated syntax for PIVOT from Microsoft

SELECT <non-pivoted column>,
[first pivoted column] AS <column name>,
[second pivoted column] AS <column name>,

[last pivoted column] AS <column name>
FROM
(<SELECT query that produces the data>)
AS source_alias
PIVOT
(
<aggregation function>(<column being aggregated>)
FOR
[<column that contains the values that will become column headers>]
IN ( [first pivoted column], [second pivoted column],
… [last pivoted column])
) AS PivotTable_alias
<optional ORDER BY clause>;

Let’s try to break this down.  What do you want in each row of the first column?  In our example, that would be years; years is the <non-pivoted column>.  Next column in the first row would be the appropriate number for client 1, as [client 1], then client 2 as [client 2] and so on.  So all you need are years, the clients and the count aggregate.  If you have other data, at least one recommendation is to create a CTE or derived table to simplify the data and the syntax.  Otherwise, you could be grouping on years, countries, states, et al as the non-pivoted columns.  You would then have 5 (years) X number of countries x number of states as the number of rows.  Client aggregates remain the pivoted columns.  If you don’t want the extra columns and wanted everything rolled up into years, don’t select the extra columns in the select FROM select query.

Next, the FROM query; give this an alias.

FROM (Select year, client_id from activities) as source.

Now the magic – immediately following the source alias, continue with (next line is OK…)

PIVOT (aggregation FOR … IN … ) as PT.  Run that as you get your desired output.

PIVOT (sum(client_id) FOR client_id IN [client_1], [client_2], [client_3], …[client_10]) as PT

Let try that again – diagram your desired output:  years are the rows, the columns are for each client and the number to display is the count of each client – for each year.  The fiddly bit is defining the column headers and putting that back into the IN clause

Advertisements