By Kristi Feng

Several weeks ago Sheila showed how to transpose GLAFS from columns to rows.

Sometimes there is also a need to transpose rows to columns.  For example, we want to see item quantities for a year in a row we can use the following SQL with ICHIST table.

The following two Transact-SQL functions are very helpful to assign a number for a partition: RANK() and ROW_NUMBER().

For example, if we want to determine what is the most purchased item (in quantity) for each customer by year, we can use the following SQL to do so.

Like always, please test all SQL statements with the actual data to catch any speed or other data related issues.