HNA071252
asked on
Dynamic Crosstab query in SQL Server 2005
I'm trying to create a dynamic crosstab query in server 2005. I copied this codes from a book and modified it to my purpose, but I couldn't get it to work. I got the error saying "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. My crosstab columns is a datetime field. Here's is the codes, please help:
DECLARE @XColumns NVARCHAR(1024)
SET @XColumns = ''
SELECT @XColumns = @XColumns + [a].[Column] + ', '
FROM
(SELECT DISTINCT [Date] AS [Column]
FROM TRP.CAP_PHID) AS a
PRINT @XColumns
SET @XColumns = Left(@XColumns, LEN(@XColumns) - 1)
SET @XColumns = 'SELECT Tab, PhID, ProdLine, Segment, '
+ @XColumns
+ ' FROM TRP.CAP_PHID PIVOT (SUM (FullProj) FOR [Date] IN ('
+ @XColumns
+ ')) AS pt'
PRINT @XColumns
EXEC sp_executesql @XColumns
DECLARE @XColumns NVARCHAR(1024)
SET @XColumns = ''
SELECT @XColumns = @XColumns + [a].[Column] + ', '
FROM
(SELECT DISTINCT [Date] AS [Column]
FROM TRP.CAP_PHID) AS a
PRINT @XColumns
SET @XColumns = Left(@XColumns, LEN(@XColumns) - 1)
SET @XColumns = 'SELECT Tab, PhID, ProdLine, Segment, '
+ @XColumns
+ ' FROM TRP.CAP_PHID PIVOT (SUM (FullProj) FOR [Date] IN ('
+ @XColumns
+ ')) AS pt'
PRINT @XColumns
EXEC sp_executesql @XColumns
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Great! It works. How do I change to show the column as "200901" (instead of 20090101, etc.)? Also how do I put this into a view so that I could link to it from Access?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great help, Thank you very much!
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.