将UDT的数据拉平

应该说，翻译不是我的擅长，转载也不是我的强项。不过这段代码还是比较有用的，就先存在这儿吧。

DNN的UDT（User Defined Table，在DNN5.*中叫Form and List)是要做成一个通用的数据存储功能，可以自定义对象的数据结构，并实现增删改操作。

在不增加新表情况下实现此类通用功能当然要借助一个列转行的概念，也就是将每个字段变成行记录，从而实现这种灵活性。就像双刃剑一样，带来缺点也是必然的，无法使用标准的SQL获得常见的行列结构数据。

下面的存储过程可以将字段数据从行记录进行合并，形成一条记录。

 

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:' rel="nofollow"' + ' + @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

 

当你执行下面语句时:

exec GetUDTData 390

将会产生下面的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