How to use pivot function to transfer these rows to columns?

Question

Went to MSDN and back, yet am still confused as ever on how to use the pivot function. How I transform the following code to transfer the rows to columns?

SELECT
 YEAR(hiredate) AS Year, COUNT(*) AS Count
FROM 
 Faculty
GROUP BY 
 YEAR(hiredate)

`SELECT * FROM Faculty PIVOT ( Count(*) FOR Year(Hiredate) IN(....) ) as p;` — vhadalgi, Oct 14 '14 at 06:59
It is saying `Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '*'.` with that code. Replaced the ellipsis with '1999','2000','2001','2003'...etc. — MrPickle5, Oct 14 '14 at 07:04

score 1 · Accepted Answer · answered Oct 14 '14 at 07:02

Something like this should do it:

SELECT  pvt.[2010], pvt.[2011], pvt.[2012], pvt.[2013], pvt.[2014]
FROM    (   SELECT  [Year] = DATEPART(YEAR, HireDate), V = 1
            FROM    Faculty
        ) AS f
        PIVOT
        (   COUNT(V)
            FOR [Year] IN ([2010], [2011], [2012], [2013], [2014])
        ) AS pvt;

PIVOT does not allow you to use a function on the column to pivoted (e.g. YEAR(HireDate)), nor does it allow COUNT(*) so you need to put the function into a subquery, and also create a column to use to count (V in this example).

How to use pivot function to transfer these rows to columns?

1 Answers1