July 13, 2011 at 2:27 pm
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
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
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
@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
Hi Suri,
have a look to this link to get idea to create a dynamic pivot query.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply