SQL 2005 CLR Pivot Update

Here's an update to my SQL Pivot function but running it on the database under the CLR. Unfortunately you have to push in the source sql which is a bit naff but quite effective. You'll need to enable the EXTERNAL permissions and enable the CLR.

EXEC sp_configure 'clr enabled', 1

RECONFIGURE WITH OVERRIDE

ALTER DATABASE SET TRUSTWORTHY ON

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class StoredProcedures
<microsoft.sqlserver.server.sqlprocedure()> _
Public Shared Sub CLRPivot(ByVal srcQuery As String, ByVal pivotColumn As String, ByVal pivotKeyColumn As String, ByVal pivotValueColumn As String, ByVal separatorStr As String)
Dim p As SqlPipe = SqlContext.Pipe
Dim connection As SqlConnection = New SqlConnection("context connection=true")
Dim connection2 As SqlConnection = New SqlConnection("Server=localhost;Database=;Trusted_Connection=True;")
connection2.Open()
Try
connection.Open()
Dim sqlCommand As SqlCommand = New SqlCommand(srcQuery)
sqlCommand.Connection = connection
Dim sqlReader As SqlDataReader = sqlCommand.ExecuteReader
Dim source As DataTable = New DataTable
source.Load(sqlReader)
Dim dest As DataTable = New DataTable
dest.Columns.Add(pivotColumn)
For Each r As DataRow In source.Rows
If r.IsNull(pivotKeyColumn) = False Then
If dest.Columns.Contains(r.Item(pivotKeyColumn).ToString) = False Then dest.Columns.Add(r.Item(pivotKeyColumn).ToString)
End If
Next
Dim dv As DataView = New DataView(source, "", pivotColumn, DataViewRowState.CurrentRows)
Dim curPivotRowItem As String = Nothing
Dim pivotedRow As DataRow = Nothing
For Each r As DataRowView In dv
If curPivotRowItem <> r.Item(pivotColumn).ToString Then
curPivotRowItem = r.Item(pivotColumn).ToString
pivotedRow = dest.NewRow
dest.Rows.Add(pivotedRow)
pivotedRow.Item(pivotColumn) = curPivotRowItem
End If
If IsDBNull(r.Item(pivotValueColumn)) = False Then
Try
pivotedRow.Item(r.Item(pivotKeyColumn).ToString) += r.Item(pivotValueColumn).ToString & separatorStr
Catch ex As Exception
End Try
End If
Next
Dim tmpName As String = "#" & Guid.NewGuid.ToString.Replace("-", "")
Dim sql As String = "CREATE TABLE " & tmpName
sql += "("
For Each col As DataColumn In dest.Columns
sql += String.Format("[{0}] varchar(2000),", col.ColumnName)
Next
sql = sql.Substring(0, sql.Length - 1)
sql += ")"
Dim createTemp As SqlCommand = New SqlCommand(sql)
createTemp.Connection = connection2
createTemp.ExecuteNonQuery()

Dim Copy As SqlBulkCopy = New SqlBulkCopy(connection2)
Copy.DestinationTableName = tmpName
Copy.WriteToServer(dest)
Dim retReaderCommand As SqlCommand = New SqlCommand(String.Format("SELECT * FROM [{0}]", tmpName))
retReaderCommand.Connection = connection2
p.Send(retReaderCommand.ExecuteReader)
Catch ex As Exception
p.Send(ex.Message)
End Try
End Sub
End Class