locked
SQL Pivot Query RRS feed

  • 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:

    Saturday, January 31, 2015 2:33 AM

Answers

  • 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

    • Marked as answer by zXSwordXz Sunday, February 1, 2015 2:25 AM
    Saturday, January 31, 2015 6:32 AM
  • @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

    • Marked as answer by zXSwordXz Sunday, February 1, 2015 11:49 PM
    Sunday, February 1, 2015 12:12 PM

All replies

  • 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

    • Marked as answer by zXSwordXz Sunday, February 1, 2015 2:26 AM
    • Unmarked as answer by zXSwordXz Sunday, February 1, 2015 4:59 AM
    Saturday, January 31, 2015 3:06 AM
  • 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

    • Marked as answer by zXSwordXz Sunday, February 1, 2015 2:25 AM
    Saturday, January 31, 2015 6:32 AM
  • @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.

    Sunday, February 1, 2015 5:04 AM
  • @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

    • Marked as answer by zXSwordXz Sunday, February 1, 2015 11:49 PM
    Sunday, February 1, 2015 12:12 PM