After working with UDT - User Defined Tables - which is now call Form and List in DNN V5.0+, I was looking for a way to convert the UDT into a conventional table.  By convention table, I mean a table with multiple data column and a single row per data item.  UDT have simple structure that places one field per data item on a row, so if you have 10 data fields then the UDT will have 10 rows per data item.  Compounding the issue is the there is only one UserDefinedTableData table.

Having developed a strong reporting module - SQLGridSelectedView (aka SGSV) - I wanted to be able to access the data in a UDT.  To do this I created a stored procedure that would generate the appropriate T-SQL and then execute it to return the flatten data.  The flatten data has one row per data item and a column for each data field.  So, instead of 10 rows per data item I have one row with 10 columns.

The stored procudure expects the ModuleID of the corresponding Forms and List (or UDT) module. The first optional parameter is the bit field that when set to 1 will return just the generated T-SQL. The default is to have the procedured execute the T-SQL and return the result set.  The second parameter defines a prefix that is prepended to each table reference. The default is "dbo.", but could be the '{databaseowner}{objectqualifier}' substitution values supported by SGSV and the Host/SQL modules.

To determine the appropriate Form and List module use the following select in Host/SQL module to determine which ModuleID to use: (Note: I have not used the '{databaseowner}{objectqualifier}'  in front of the table names)

Select ModuleID, ModuleTitle from TabModules where ModuleID in (
 Select ModuleID from Modules where ModuleDefID in (
  Select ModuleDefID from dbo.ModuleDefinitions where DesktopModuleID in (
   Select [DesktopModuleID] from [dbo].[DesktopModules]
          where FriendlyName = 'Form and List')))

The procedure is written for SQL2000 as my production environment is still SQL2000 (ugh!).  Also I not used the '{databaseowner}{objectqualifier}' substitution values when defining the procedure.  

Update: Based on Mark's comments I have revised the script to use nvarchar(1000) and have also supported Email field type.

Create Procedure dbo.GetUDTData
  @ModuleID int,
  @ReturnSelect bit = 0,
  @Prefix varchar(100) = 'dbo.'
as

-- Revision History
-- 03-sep-2010 Added @CastFieldValue to allow easy change to way FieldValue is Cast
--                - could use nvarchar(max) in SQL2005+

-- exec dbo.GetUDTData 418
-- exec dbo.GetUDTData 418, 1
-- exec dbo.GetUDTData 418, 0, '{databaseowner}{objectqualifier}'

DECLARE @UserDefinedFieldId int
DECLARE @FieldTitle varchar(50)
DECLARE @FieldType  varchar(20)
DECLARE @Visible  bit

DECLARE @CastFieldValue varchar(100)
DECLARE @SQL Varchar(8000)
DECLARE @CRLF Char(2)

set nocount on

DECLARE GetFieldsCursor INSENSITIVE CURSOR
    FOR 
    SELECT UserDefinedFieldId, FieldTitle, FieldType, Visible
    from UserDefinedFields
    where ModuleID = @ModuleID
    and Visible = 1
    and FieldType <> 'Expression'
    order by FieldOrder
   
OPEN GetFieldsCursor

FETCH NEXT FROM GetFieldsCursor INTO  @UserDefinedFieldId, @FieldTitle, @FieldType, @Visible

Set @CRLF = CHAR(13) + CHAR(10)

Set @CastFieldValue = 'Cast(FieldValue as nvarchar(1000))'
Set @SQL = 'Select UserDefinedRowId as RowID' + @CRLF

WHILE @@FETCH_STATUS = 0
BEGIN

  Set @SQL = @SQL + '  ,(Select '
 
  Set @SQL = @SQL +
            Case @FieldType
              when 'Decimal'   then 'Cast(' + @CastFieldValue + ' as Decimal)'
              when 'Date'      then 'Cast(' + @CastFieldValue + ' as DateTime)'
              when 'DateTime'  then 'Cast(' + @CastFieldValue + ' as DateTime)'
              when 'CreatedAt' then 'Cast(' + @CastFieldValue + ' as DateTime)'
              when 'ChangedAt' then 'Cast(' + @CastFieldValue + ' as DateTime)'
              when 'Email'     then '''mailto:'' + ' + @CastFieldValue + ' +  ''">'' + ' + @CastFieldValue + ' + '''''
              when 'Url'       then ''''' + ' + @CastFieldValue + ' + '''''
              else '' + @CastFieldValue + ''
            End
  Set @SQL = @SQL + ' from ' + @Prefix + 'UserDefinedData where UserDefinedFieldId = '
  Set @SQL = @SQL + CAST(@UserDefinedFieldId as varchar) + ' '
  Set @SQL = @SQL + 'and UserDefinedRowId = UDR.UserDefinedRowId'
  Set @SQL = @SQL + ') as [' + @FieldTitle + ']' + @CRLF
 
  FETCH NEXT FROM GetFieldsCursor INTO  @UserDefinedFieldId, @FieldTitle, @FieldType, @Visible
END

Set @SQL = @SQL + 'from ' + @Prefix + 'UserDefinedRows UDR where ModuleId = '
Set @SQL = @SQL + CAST(@ModuleID as varchar) + @CRLF

CLOSE GetFieldsCursor
DEALLOCATE GetFieldsCursor

if @ReturnSelect = 1
  begin
  Print @SQl
  Select @SQL
  end
else
  execute(@SQL)

return @@rowcount
GO

The stored procedure has some limitation, specifically, it does not support expression.  This may not be an issue as the user can take the generated T-SQL and revise it as necessary to generate the calculated column.

For simple example, Assume module 390 is a UDT with the structure of

EngineID	Text
EngineType	Text
Manufacturer	Text
PurchaseDate	Date
Cost	Decimal
Website	Url

The typical execution of the procedure would something like

exec GetUDTData 390

which would generate and execute the follow T-SQL:

Select UserDefinedRowId as RowID
  ,(Select Cast(FieldValue as varchar) from dbo.UserDefinedData where UserDefinedFieldId = 5 and UserDefinedRowId = UDR.UserDefinedRowId) as [EngineID]
  ,(Select Cast(FieldValue as varchar) from dbo.UserDefinedData where UserDefinedFieldId = 6 and UserDefinedRowId = UDR.UserDefinedRowId) as [EngineType]
  ,(Select Cast(FieldValue as varchar) from dbo.UserDefinedData where UserDefinedFieldId = 7 and UserDefinedRowId = UDR.UserDefinedRowId) as [Manufacturer]
  ,(Select Cast(Cast(FieldValue as varchar) as DateTime) from dbo.UserDefinedData where UserDefinedFieldId = 8 and UserDefinedRowId = UDR.UserDefinedRowId) as [PurchaseDate]
  ,(Select Cast(Cast(FieldValue as varchar) as Decimal) from dbo.UserDefinedData where UserDefinedFieldId = 9 and UserDefinedRowId = UDR.UserDefinedRowId) as [Cost]
  ,(Select '' + Cast(FieldValue as varchar) + '' from dbo.UserDefinedData where UserDefinedFieldId = 11 and UserDefinedRowId = UDR.UserDefinedRowId) as [website]
from dbo.UserDefinedRows UDR where ModuleId = 390

You can either use the stored procedure or the T-SQL generated by the stored procedure in whatever report module you are using (such as SGSV).

Hope this Helps
Paul.