This gives a graphical explanation of how the grouping, spreading and aggregation columns convert from the source to pivoted tables if that helps further.

Using the grouping, spreading, aggregation terminology you will typically see example syntax for a pivot as: WITH PivotData AS Or in your case, ElementID and PhaseIDX lookup Effort. Once this pivoting is done, the grouping and spreading columns are used to find an aggregation value. Although in the example given the aggregation that will be used is unclear, but involves the Effort column. Typically you also then need to provide some form of aggregation that gives you the values referenced by the intersection of the spreading value ( PhaseID) and the grouping value ( ElementID). These pivoted values are grouped via the ElementID column in the example that you have given. In the example you have given, this means converting the PhaseID rows into a set of columns, where there is one column for each distinct value that PhaseID can contain - 1, 5 and 6 in this case. SELECTĪ pivot is used to convert one of the columns in your data set from rows into columns (this is typically referred to as the spreading column).

Second solution is to use a group by and do a sum of the pivoted column values again. In order to fix this, you can either pull a subquery as a source as everyone has done above - with only 3 columns (this is not always going to work for your scenario, imagine if you need to put a where condition for the timestamp). FROM (SELECT CUST, Product, QTY FROM PRODUCT) p See this example, ive added a timestamp column to the source table: However, if you have more columns in the source it will break the results into multiple rows instead of one row per pivot based on unique values per additional column (as Group By would do in a simple query). In the product example it's QTY, CUST, PRODUCT.

The pivot function works great when the source has 3 columns: One for the aggregate, one to spread as columns with for, and one as a pivot for row distribution. Ive something to add here which no one mentioned. ) p results for both: ELEMENTID PHASEID1 PHASEID5 PHASEID6 Select = STUFF((SELECT distinct ',' + QUOTENAME(c.phaseid) This can also be done through a dynamic PIVOT where you create the list of columns dynamically and perform the PIVOT. Here is a SQL Demo with a working version. A PIVOT used to rotate the data from one column into multiple columns.įor your example here is a STATIC Pivot meaning you hard code the columns that you want to rotate: create table temp

Pivot tables for dummies