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 PIVOT
is:
From MSDN:
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 MIN()
or 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 GROUP BY
.
For your example, you can use row_number()
:
SELECT ANALYST, CLERK, MANAGER, PRESIDENT, SALESMAN
from
(
SELECT ename, job,
row_number() over(partition by job order by ename) rn
from emp
) as st
pivot
(
max(ename)
FOR job in (ANALYST, CLERK, MANAGER, PRESIDENT, SALESMAN)
) as pivottable
See SQL Fiddle with Demo.
The 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.
Vishwanath Dalvi
SOreadytohelp about me box is kept "", intentionally.
Updated on March 15, 2020Comments
-
Vishwanath Dalvi about 4 years
JOB ENAME -------- ---------- ANALYST SCOTT ANALYST FORD CLERK SMITH CLERK ADAMS CLERK MILLER CLERK JAMES MANAGER JONES MANAGER CLARK MANAGER BLAKE PRESIDENT KING SALESMAN ALLEN SALESMAN MARTIN SALESMAN TURNER SALESMAN WARD
I would like to format the result set such that each job gets its own column:
CLERKS ANALYSTS MGRS PREZ SALES ------ -------- ----- ---- ------ MILLER FORD CLARK KING TURNER JAMES SCOTT BLAKE MARTIN ADAMS JONES WARD SMITH
I tried
SELECT ANALYST, CLERK, MANAGER, PRESIDENT, SALESMAN from ( SELECT ename, job from emp ) as st pivot ( SELECT ename FOR job in (ANALYST, CLERK, MANAGER, PRESIDENT, SALESMAN) ) as pivottable
I'm getting these errors
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'SELECT'.
Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'in'.How to use pivot to group strings under pivot column?
-
Shaibal Ahmed over 3 yearsWhat a wonderful solution. Thank you so much Taryn.