Example for dynamic pivot table (crosstab) in SQL Server 2005

-- traffic distribution by devip and protocol
declare @list varchar(max) -- select distinct protocol from #1 order by 1 SELECT @list = COALESCE(@list + ',[', '[') + CAST(protocol AS varchar(4)) + ']' FROM (select distinct top 100 protocol from #1 order by 1) A -- select @list exec('select * into ##2 from (select DevIP,Protocol,Octets from #1) A pivot( sum(octets) for protocol in ('+@list+')) as p') select * from ##2 drop table ##2
This entry was posted in SQL Server 2005. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s