Pivoting Without Aggregation

The PIVOT operator is a useful tool. It lets you aggregate and rotate data so that you can create meaningful tables that are easy to read. However, there are times when you might not want to aggregate data while pivoting a table.

For example, you might want to simply pivot the values in Table 1 so that each team has its members in one row, as Table 2 shows.

But as the following basic syntax shows

   PIVOT
   (Aggregate function (column1)
     FOR column2
     IN ( \[val1\], \[val2\], \[val3\] )) AS P

   where
   •  column1 is the column you want to aggregate
   •  column2 is the column you want to pivot
   •  \[val1\], \[val2\], and \[val3\] are the headings for the pivoted columns
   •  P is the alias for the results of the PIVOT expression

the PIVOT expression requires an aggregate function.

I've developed a solution that lets you pivot data without aggregating it. Listing 1 illustrates this solution using the data in Table 1.

Listing 1: Code That Pivots a Table Without Aggregating Data

The SELECT statement in callout B is key to this workaround. In this code, I query the tables' Team and Member columns as well as the ROW_NUMBER function. I use the OVER clause with this function so that I can partition and order the function's result set by teams. This groups the members into their respective teams (CRM and ERP) and, within each team, gives members a number that specifies their position in that group (i.e., an ordinal number). Table 3 shows the result set produced by this SELECT statement.

Table 3: Result Set Produced by the SELECT Statement in Callout B

Because each ordinal number is associated with only one member in each team, it's now possible to use the MAX aggregate function in the PIVOT operation. (The maximum value of a data set with only one member will always be that member.) So, in the PIVOT expression in callout C, I use

PIVOT (MAX(Member)

to aggregate the Member column. I want to pivot the RowNum column, which I do with the code

FOR RowNum

In the last segment of the PIVOT expression

IN (\[1\], \[2\], \[3\])) AS pvt

I use aliases for the pivoted column headings. The actual column headings are provided in the SELECT statement in callout A. Note that when a value that will end up as column name doesn't follow the rules for regular identifiers, you must enclose it in brackets (\[ \]). Finally, I assign the PIVOT expression's results to pvt.

As Listing 1 demonstrates, although you can't take away the aggregate function in a PIVOT expression, you can take away the aggregate function's effect. If you'd like to try the code in Listing 1, you can download it by clicking the "Download the Code" link at the top of the page. It works on SQL Server 2005 and later.

Comments

Plain text