PIVOT Operator with Dynamic Columns

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » PIVOT Operator with Dynamic Columns

PIVOT Operator with Dynamic Columns

Rate Topic

Display Mode

Topic Options

Author

Message

scott.k.ballard

Posted Friday, February 22, 2013 3:09 PM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, February 23, 2013 7:33 AM
Points: 1, Visits: 2

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

LutzM

Posted Saturday, February 23, 2013 3:05 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 3:17 PM
Points: 6,731, Visits: 12,131

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 »

Permissions