I have the below data in MS SQL:

Now, I would like to create a Pivot query base on the "CostCenterNumber" field  to look like this:

You query should look like as below. Replace table name.

DECLARE
    @cols nvarchar(max),
    @stmt nvarchar(max)
SELECT @cols = isnull(@cols + ', ', '') + '[' + T.CostCenterNumber + ']' FROM (SELECT distinct CostCenterNumber FROM TableName) as T
SELECT @stmt = '
    SELECT *
    FROM TableName as T
        PIVOT 
        (
            max(T.Total)
            for T.[CostCenterNumber] in (' + @cols + ')
        ) as P'
exec sp_executesql  @stmt = @stmt

Another way is this

SELECT [Year],
GLClass,
Code,
GLDescription,
SUM(CASE WHEN CostCenter = 39401 THEN Total END) AS 39401,
SUM(CASE WHEN CostCenter = 39402 THEN Total END) AS 39402,
SUM(CASE WHEN CostCenter = 39404 THEN Total END) AS 39404,
SUM(CASE WHEN CostCenter = 39405 THEN Total END) AS 39405
FROM table
GROUP BY [Year],
GLClass,
Code,
GLDescription

to make it dynamic see

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

@Rajen Singh,

Why is it repeating every record?  It should  find the unique record by Year, GLClass, Code, GLDescription.

I would love to know how to do it with dynamic columns like your solution.

Thank you.