Link to home
Start Free TrialLog in
Avatar of HNA071252
HNA071252Flag for United States of America

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
ASKER CERTIFIED SOLUTION
Avatar of ralmada
ralmada
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of A071252
A071252

How do I use this code? I executed in the SQL Server window and I got this message:
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of HNA071252

ASKER

Great help, Thank you very much!