In the end I decided not to do the cross-tab in dynamic SQL because there can be permission problems with dynamic SQL that don't occur with ordinary stored procedures. Instead I used the following code which was obviously the source for the cross-tab in dnn's User Defined Tables. Now all is working fine.
''' -----------------------------------------------------------------------------
''' <summary>
''' Pivot transforms a normalized datareader into a pivoted datatable
''' </summary>
''' <param name="dataValues">DataReader to be pivoted into DataTable</param>
''' <param name="keyColumn">column in DataReader for each row</param>
''' <param name="pivotNameColumn">values to turn from rows to column names</param>
''' <param name="pivotValueColumn">values to pivot into appropriate columns</param>
''' <returns>The generated datatable</returns>
''' <history>From: Transforming a Normalized DataReader into a Pivoted DataTable
''' at http://weblogs.sqlteam.com/jeffs/archive/2005/05/11/5101.aspx
''' </history>
''' -----------------------------------------------------------------------------
Public Shared Function Pivot(ByVal dataValues As IDataReader, ByVal keyColumn As String, ByVal pivotNameColumn As String, ByVal pivotValueColumn As String) As DataTable
Dim tmp As New DataTable()
Dim r As DataRow
Dim LastKey As String = "//dummy//"
Dim i, pValIndex, pNameIndex As Integer
Dim s As String
Dim FirstRow As Boolean = True
' Add non-pivot columns to the data table
pValIndex = dataValues.GetOrdinal(pivotValueColumn) 'IssueId
pNameIndex = dataValues.GetOrdinal(pivotNameColumn) 'Issue name
For i = 0 To dataValues.FieldCount - 1
If i <> pValIndex And i <> pNameIndex Then
tmp.Columns.Add(dataValues.GetName(i), dataValues.GetFieldType(i))
End If
Next i
r = tmp.NewRow()
' now, fill up the table with the data:
While dataValues.Read()
' see if we need to start a new row
If dataValues(keyColumn).ToString() <> LastKey Then
' if this isn't the very first row, add last one to the table
If Not FirstRow Then
tmp.Rows.Add(r)
End If
r = tmp.NewRow()
FirstRow = False
' Add all non-pivot column values to the new row
For i = 0 To dataValues.FieldCount - 3
r(i) = dataValues(tmp.Columns(i).ColumnName)
Next i
LastKey = dataValues(keyColumn).ToString()
End If
' assign pivot values to proper column, add new columns if needed
s = dataValues(pNameIndex).ToString()
If Not tmp.Columns.Contains(s) Then
tmp.Columns.Add(s, dataValues.GetFieldType(pValIndex))
End If
r(s) = dataValues(pValIndex)
End While
' add that final row to the datatable
tmp.Rows.Add(r)
' Close the DataReader
dataValues.Close()
Return tmp
End Function
Hope this is helpful to someone else.
Geoffrey
PS look up the article in the above comments for details of how to prepare the SQL input for this.