SQL 2005 DataTable Pivots

When I saw the SQL 2005 PIVOT function I thought great at last an easy way to PIVOT on the DB, but to be honest it sucks for anything but basic operations. I then thought I'd have ago at integration services - nice piece of software but still falls short in terms of the PIVOT function.

One of the fundamental issues with pivotting large lumps of data is that you don't know the number of columns you're going to end up.

The only sane way to do this is to code it into a custom assembly.

This is my take on it wrapped into a function.

Public Function PivotDataTable(ByVal source As DataTable, ByVal pivotCol As String, ByVal keyCol As String, ByVal pivotValueCol As String, ByVal separatorStr As String) As DataTable
Dim dest As DataTable = New DataTable
dest.Columns.Add(pivotCol)
For Each r As DataRow In source.Rows
If r.IsNull(keyCol) = False Then
If dest.Columns.Contains(r.Item(keyCol)) = False Then
dest.Columns.Add(r.Item(keyCol))
End If
End If
Next
Dim dv As DataView = New DataView(source, "", pivotCol, DataViewRowState.CurrentRows)
Dim curPivotRowItem As String = Nothing
Dim pivotedRow As DataRow = Nothing
For Each r As DataRowView In dv
If curPivotRowItem <> r.Item(pivotCol).ToString Then
curPivotRowItem = r.Item(pivotCol).ToString
pivotedRow = dest.NewRow
dest.Rows.Add(pivotedRow)
pivotedRow.Item(pivotCol) = curPivotRowItem
End If
If IsDBNull(r.Item(pivotValueCol)) = False Then
pivotedRow.Item(r.Item(keyCol)) += r.Item(pivotValueCol) & separatorStr
End If
Next
Return dest
End Function

The function is based around the pretense that all fields are nvarchar of some sort. As with all Pivot examples I've seen, the weekness in the functions is the use case scenario, so i'll try and help you all with a decent example.

Take a source table that looks like this:

TableIDColumn	PivotColumn	PivotkeyCol	pivotValueColumn
1	2	Bar	a
2	3	BarFoo	b
3	3	Foo	c
4	5	Bar	d
5	5	FooBar	e

we're now going to pivot the table about the PivotColumn column such that a new column will be created for every distinct entry in PivotkeyCol column and the value in the pivotValueColumn will be placed in the new row and column entry.
so that we will end up with the following table:

PivotColumn	Bar	BarFoo	Foo	FooBar
2	a	null	null	null
3	null	b	c	null
5	d	null	null	e