February 22, 2013 at 3:09 pm
The following syntax works except I would like to add a text string(Time) to the beginning of the field names created by the pivot operator. I have added a commented out line of code starting at COALESCE which has the syntax I attempted to use to add the string to the beginning of the field name. It adds the text string to the field name but the syntax fails to add values into these fields, they are all null. Any suggestions?
DECLARE @columns VARCHAR(8000)--declare variable to store all the date values in column Date
SELECT @columns =
--COALESCE(@columns + ',[' + 'Time' + cast(DATEPART(week, Date) as varchar) + ']','[' + 'Time' + cast(DATEPART(week, Date) as varchar)+ ']')
COALESCE(@columns + ',[' + cast(left(Date, 11) as varchar) + ']','[' + cast(left(Date, 11) as varchar)+ ']')
FROM Avg_Utilization group by left(Date, 11) order by LEFT(DATE, 11)
select @columns
declare @query varchar(8000)
SET @query ='select * into avgpivotjunk from
(select node, left(date, 11) as Date, avg_utilization from Avg_Utilization)a
pivot
(
sum(Avg_Utilization) for date in('+ @columns +')
)as p'
execute (@query)
February 23, 2013 at 3:05 am
You might want to look into the Dynamic Cross Tab approach as referenced in my signature.
Might be the less strugglig way to achieve what you're looking for.
As a side note: I'd recommend you get used to use sp_executesql instead of Exec() due to security issues (SQL injection). It might not be an issue in this case, but in another one you might need to deal with...
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply