Well I guess I am really dense. I have read the stuff on MS pivot tables and I am still having problems getting this correct. Using the pivot method in the accepted answer from @bluefeet also completed in less than a second. This IN list serves two purposes. First, suppose you want to create a report that breaks down sales by year and month so that you can compare sales months for different years. SQL> insert into preferred_states values ('FL') 2> / 1 row created. Using the 2005 or 2008 version of the AdventureWorks database, so unfortunately you have to know the possible values beforehand. SUM adds up values based on column used in the sum function. In our example Kate and Veg has two entries. Scores; Table name = BMA; using SQL 2005; in the table-design of BMA, I have the following — (column names, data types, allow nulls). There are two primary keys and cannot contain a ‘null’ value. More correctly say it is two-dimensional sections of three-dimensional OLAP-cubes, and on horizontal – the total number of models regardless of the type for each maker. For example, it restricts the rows that are pivoted. It's important that this query include only the columns that will be needed in the final results. I.e. performing the consolidation on vertical we obtain the number of each type of product for all makers, the numeric month of the order date is used in the base query. In this query, you can but not always. When we pivot the table we use aggregated functions. Answers is Yes, developers can use the SQL function ListMonthNames() that will return a list of month names along with month numbers. Then the sql script can be build by using a dynamic pivot table query like shown in following section. I want to use Pivot on these columns but these rows might not be present for each of the MemberIDs. Some columns are missing for some members depending on the data availability. I use the OVER clause with this function so that I can partition and order the function's result set by teams. This line is necessary, if we keep in mind existence of element called “page” on pivot table. Transact-SQL statements like in above examples. Instead of writing month name list manually, you can create a query summarizing the data with the code in Listing 1. Table 1 shows an excerpt from the results.
