SQL Pivot Query

Question

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:

Answer 1

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

---

Regards, RSingh

Answer 2

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

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Answer 3

@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.

Answer 4

@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.

Please note that I have used "*" to select the fields in the PIVOT. You can try choosing the fields as below. Do you mean providing dynamic columns for pivot like 'Year, GLClass, Code, GLDescription' ? If yes then you can declare a substring and concatenate within the main query.

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 (SELECT Year, GLClass, Code, GLDescription FROM TableName) as T
        PIVOT
        (
            max(T.Total)
            for T.[CostCenterNumber] in (' + @cols + ')
        ) as P'
exec sp_executesql  @stmt = @stmt

---

Regards, RSingh