PROBLEM WITH PIVOT TABLE

[spiral123]

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT TOP 4 @PivotColumnHeaders =
COALESCE(
@PivotColumnHeaders + ',[' + cast([TimeStamp] as varchar) + ']',
'[' + cast([TimeStamp] as varchar)+ ']'
)
FROM RecentTimestamps WHERE [SiteID] = @SiteID Order By [TimeStamp] Desc

DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'

SELECT *
FROM (SELECT [Header], [Value], [TimeHeader] FROM TimeDataGather Where [SiteID] = ' + @SiteID + ' ) as s PIVOT (Max([Value]) FOR [TimeHeader] IN (' + @PivotColumnHeaders + ')) AS p
Order By [Header] Desc'

EXECUTE(@PivotTableSQL)
END

 

[markros]

What exactly is the problem?

Instead of Execute (@PivotTableSQL)

do

print @PivotTableSQL

and then try running it

It would be much easier to spot the problem.

PluralSight Learning Library

 

[spiral123]

found my issue, I used cast([TimeStamp] as varchar) in the headers but my [Timeheader] field was still datetime thanks though.

 

[markros]

BTW, when you're casting to varchar always specify the size.

Check out this blog

http://blogs.lessthandot.com/index....rver/always-include-size-when-using-varchar-n

PluralSight Learning Library