Post reply

SQL Pivot

  • July 13, 2011 at 2:27 pm

    #254410

    Hi All,

    I am trying to pivot in SQL. I want dates as the two pivoted columns. The code runs fine when I give static values in the IN clause inside the pivot operator and the pivoted columns (eg: [2011-07-09]).

    When I try to assign variables(@pivDate1 and use them in place of the static values, it fails.

    DECLARE @pivDate1 DATETIME = (SELECT TOP 1 PulledDate FROM #FinalOutput fo ORDER BY 1 ASC)

    DECLARE @pivDate2 DATETIME = (SELECT TOP 1 PulledDate FROM #FinalOutput fo ORDER BY 1 DESC)

    SELECT Category , [@pivDate1], [@pivDate2]

    FROM

    (SELECT Category,TotalNumber,PulledDate AS PulledDate FROM #FinalOutput) AS SourceTable

    PIVOT

    (

    SUM(TotalNumber)

    FOR PulledDate IN ([@pivDate1],[@pivDate2])

    -- This works([2011-07-09],[2011-07-02])

    ) AS PivotTable ;

    Error I get

    Error converting data type nvarchar to datetime

    The incorrect value @pivDate1 is supplied in the PIVOT operator.

    The column PulledDate has been declared with Data type Datetime in #FinalOutput table.

    --[2011-07-09],[2011-07-02] works fine.

    Any help will be greatly appreciated.

  • July 13, 2011 at 2:59 pm

    #1352488

    I would suggest you build your query as a string, and execute it as dynamic sql.

    Heading out now, but I may post an example later tonight.

  • July 13, 2011 at 3:32 pm

    #1352502

    Thanks Nevyn.

    I found this helpful link.

    http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx

  • July 13, 2011 at 4:15 pm

    #1352522

    @suri.yalamanchili, please consider using a Dynamic Cross Tab query instead of building a query with the PIVOT operator:

    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs[/url]

    The article above is Part 2 of a 2-part set. Please read Part 1 to see the details of why there is a performance advantage when using Cross Tab queries.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/url]

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • July 14, 2011 at 12:27 am

    #1352582

    Hi Suri,

    have a look to this link to get idea to create a dynamic pivot query.

    http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply