Pivot table strings grouping under pivot column?
When you are using the
PIVOT function, you are required to use an aggregate function. The syntax of a
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 <alias for the source query>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 <alias for the pivot table><optional ORDER BY clause>;
With a string, you will need to use either the
MAX() aggregate function. The problem that you will run into is that these functions will return only one value for each column.
So in order to get the
PIVOT to work, you will need to provide a distinct value that will keep the rows separate during the
For your example, you can use
SELECT ANALYST, CLERK, MANAGER, PRESIDENT, SALESMAN from( SELECT ename, job, row_number() over(partition by job order by ename) rn from emp) as stpivot( max(ename) FOR job in (ANALYST, CLERK, MANAGER, PRESIDENT, SALESMAN)) as pivottable
See SQL Fiddle with Demo.
row_number() creates a distinct value that is assigned to each row in the
job, when you apply the aggregate function and the
GROUP BY in the
PIVOT you will still get separate rows.