Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

PIVOT Operator with Dynamic Columns Expand / Collapse
Author
Message
Posted Friday, February 22, 2013 3:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 26, 2013 4:39 PM
Points: 1, Visits: 9
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)
Post #1423275
Posted Saturday, February 23, 2013 3:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 13, 2014 9:47 AM
Points: 6,842, Visits: 13,368
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...




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1423332
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse