使用动态 SQL 将数据从行转换为列
一个使用动态 SQL 将数据从行转换为列的自定义解决方案。
引言
在使用关系数据库中的数据时，非规范化形式（如电子表格）通常能给我们带来更好的展示和理解。开发人员面临的挑战之一是将数据从行转换为列。在本文中，我将讨论一个使用动态 SQL 的自定义解决方案。
要解决的问题
创建了一个虚构的数据库 **EmployeeDB**，它保存了员工的联系方式、教育和工作经历信息。有三个源表：`Employee`、`Education` 和 `Jobhistory`，它们保存关系数据；还有三个目标表：`Trans_Employee`、`Trans_Education` 和 `Trans_JobHistory`，其中保存了从行转换为列的数据。让我们看下面的截图。它显示了 EmployeeID=3 的教育记录。
图像的顶部列出了三条记录，包含三列：`SchoolName`、`Degree` 和 `YearOfGraduation`。它们是从 `Education` 表中选择的。底部显示了保存在目标表 `Trans_Education` 中的转换数据。彩色框和箭头表示 `Education` 表中的行到 `Trans_Education` 表中列的位置。3 行被转换为 9 列。
这两个表定义如下。
Education（源）
Trans_Education（目标）
在目标表 `Trans_Education` 中可以看到，列名遵循命名约定
A columnname from_Education_table
+ a hyphen(-)
+ a corresponding trailing number(1, 2, or 3)
尾随数字与从 `Education` 表中选定记录的行号匹配。换句话说，第一行放置到带有尾随数字“-1”的第一组 3 列中，第二行放置到带有尾随数字“-2”的组中，第三行放置到带有尾随数字“-3”的组中。我们这里最多只转换三行。因此，在目标 `Trans_Education` 表中，有三组列分别带有尾随数字 1、2 和 3。如果需要转换更多行，则必须向 `Trans_Education` 表中添加更多列。
员工的工作经历记录包含五列（不包括两个 ID 列）。与上述类似，最多从源 `JobHistory` 表中选择 3 条记录，并将其转换为 `Trans_JobHistory` 表中的 15 列。显示数据转换的屏幕截图，以及源 `JobHistory` 和目标 `Trans_JobHistory` 的定义如下所示。
JobHistory（源）
Trans_JobHistory（目标）
对于员工联系信息，我们将把源 `Employee` 表中的 1 行转换为 `Trans_Employee` 表中的 6 列。数据转换的屏幕截图，以及源 `Employee` 表和目标 `Trans_Employee` 表如下所示。
Employee（源）
Trans_Employee（目标）
尽管转换只涉及 1 行，但目标 `Trans_Employee` 表中使用了相同的命名约定，以便使用稍后将讨论的动态 SQL 实现一定程度的自动化。
将行转换为列的逻辑步骤
现在让我们看看将源 `Education` 表中的行转换为 EmployeeID=3 的目标 `Trans_Education` 表中的列所需的逻辑步骤。
步骤 1：向目标 `Trans_Education` 表中插入一条新记录，其中 EmployeeID 列的值为 3，其余列为 NULL（默认值）。NULL 列稍后将根据从源 Education 表中提取的相应行进行更新。如果此员工的记录已存在于目标表中，则应在插入新记录之前删除该记录。
列表 1
DELETE FROM Trans_Education WHERE EmployeeID = 3
INSERT INTO Trans_Education(EmployeeID) VALUES( 3 )
步骤 2：从 `Education` 表中拉取前 3 条记录，并将它们放入一个全局临时表 `##GlobalTempTable` 中。
列表 2
SELECT TOP 3 [SchoolName],[Degree],[YearOfGraduation]
INTO ##GlobalTempTable
FROM [Education] WHERE EmployeeID=3
ORDER BY YearOfGraduation DESC
使用全局临时表可能看起来不是必需的。然而，当所有必需的 SQL 语句都准备好并放置到它们各自的存储过程中时，在一个存储过程中创建的局部临时表在另一个存储过程中就超出了作用域。全局临时表避免了这个问题。
步骤 3：声明一个由 `##GlobalTempTable` 中的数据填充的 SQL 游标。遍历每条记录以更新 `Trans_Education` 表中步骤 1 中创建的记录的相关列。
列表 3
DECLARE @p1 VARCHAR(250), @p2 VARCHAR(250), @p3 VARCHAR(250)
DECLARE @Counter INT, @UpdateRevised NVARCHAR(4000), @Params NVARCHAR(4000)
SELECT @Counter = 0 , @params = '@p1 varchar(250), @p2 varchar(250), @p3 varchar(250)'
--declare a cursor
DECLARE DataCursor CURSOR FOR
SELECT [SchoolName],[Degree],[YearOfGraduation]
FROM ##GlobalTempTable
OPEN DataCursor;
FETCH NEXT FROM DataCursor INTO @p1, @p2, @p3
WHILE @@Fetch_Status = 0
BEGIN
SELECT @Counter = @Counter + 1
SELECT @UpdateRevised =
REPLACE('update Trans_Education
set [SchoolName-0]=@p1,
[Degree-0]=@p2,
[YearOfGraduation-0]=@p3
Where EmployeeID=3','-0','-' + CONVERT(VARCHAR, @Counter))
EXECUTE sp_ExecuteSql @UpdateRevised, @params, @p1, @p2, @p3
FETCH NEXT FROM DataCursor INTO @p1, @p2, @p3
END
CLOSE DataCursor
DEALLOCATE DataCursor
DROP TABLE ##GlobalTempTable
在清单 3 中，执行一个 SQL 系统存储过程 `sp_ExecuteSql` 来更新相关列。该存储过程具有以下语法
EXECUTE sp_ExecuteSql
@UpdateRevised, -- SQL statement with embedded parameters
@params, --Parameter definition list separated by comma
@p1, @p2, @p3 --Parameter value list separated by comma
参数 `p1`、`p2`、`p2` 和 `params` 根据系统存储过程的要求声明。只声明了三个参数 `p1`、`p2` 和 `p3` 而不是更多或更少的原因是每行包含三列。
初始更新语句是
列表 4
update Trans_Education
set [SchoolName-0]=@p1, [Degree-0]=@p2, [YearOfGraduation-0]=@p3
Where EmployeeID=3
注意列名带有尾随数字“-0”。在第一次抓取中，`@p1`、`@p2` 和 `@p3` 被赋予游标中第一条记录的值，并且尾随数字“-0”被“-1”替换。结果，语句变为
列表 5
--the first fetch
update Trans_Education
set [SchoolName-1]=@p1, [Degree-1]=@p2, [YearOfGraduation-1]=@p3
Where EmployeeID=3
修改后的更新语句放在参数 `UpdateRevised` 中，然后通过系统存储过程 `sp_ExecuteSql` 执行，以更新 `Trans_Education` 表中带有尾随数字“-1”的第一组 3 列。随后，在接下来的两次抓取中，通过参数 `Counter` 增加尾随数字，并修改更新语句，以分别更新带有尾随数字“-2”和“-3”的其他列，如清单 6 所示。
列表 6
--the second fetch
update Trans_Education
set [SchoolName-2]=@p1, [Degree-2]=@p2, [YearOfGraduation-2]=@p3
Where EmployeeID=3
--the third fetch
update Trans_Education
set [SchoolName-3]=@p1, [Degree-3]=@p2, [YearOfGraduation-3]=@p3
Where EmployeeID=3以上三个步骤已将员工的教育记录从行转换为列。以类似的方式，可以准备 SQL 脚本来转换员工的工作经历记录。在这种情况下，应声明五个参数 `p1` 到 `p5`。应从 `JobHistory` 表中拉取前 3 条记录并保存到 `##GlobalTempTable` 中。更新语句根据 `Trans_JobHistory` 表的定义编写。
对于员工联系信息，应声明六个参数 `p1` 到 `p6`。应拉取并保存前 1 条记录。其更新语句基于 `Trans_Employee` 表的定义。
显然，这些都是重复性任务，涉及不同数量的参数和与特定目标表关联的不同更新语句。与其一遍又一遍地重写类似的 SQL 语句，我们需要一种自动化过程的方法。这时动态 SQL 就派上用场了。
动态 SQL
在 SQL 数据库中，用户定义表的定义保存在系统表中。我们可以从系统表中查看定义，例如列名、列数、数据类型、大小等。有了这些信息，就可以动态构建 SQL 语句，将数据从行转换为任何相关目标表的列。
清单 7 中的存储过程 `sp_EmployeeDB_DataTransformation_Data_Processing` 提供了完整的 SQL 脚本，用于动态构建和执行数据转换的 SQL 语句。
列表 7
CREATE PROCEDURE [dbo].[sp_EmployeeDB_DataTransformation_Data_Processing]
@EmployeeID int
,@TargetTableName VARCHAR(100)--Trans_Employee, Trans_Education or Trans_JobHistory table
AS
BEGIN
SET NOCOUNT ON
--remove the record created in a previous execution from the target table
DECLARE @SqlTemp VARCHAR(1000)
SELECT @SqlTemp='delete from ' + @TargetTableName + ' where EmployeeID=' + CONVERT(VARCHAR, @EmployeeID) + CHAR(10)
--insert a record into the target table with only EmployeeID (other fields default to null)
SELECT @SqlTemp=@SqlTemp + 'insert into ' + @TargetTableName + '(EmployeeID) values(' + CONVERT(VARCHAR, @EmployeeID) + ')'
EXECUTE(@SqlTemp)
--place all records retured into the ##GlobalTempTable
IF @TargetTableName='Trans_Employee'
BEGIN
EXEC sp_EmployeeDB_Employee_Select @EmployeeID
END
IF @TargetTableName='Trans_Education'
BEGIN
EXEC sp_EmployeeDB_Education_Select @EmployeeID
END
IF @TargetTableName='Trans_JobHistory'
BEGIN
EXEC sp_EmployeeDB_JobHistory_Select @EmployeeID
END
--if more tables are required, add similar statement as the above
--pull the firs set of column names (ending with '-1') into a table variable.
DECLARE @Tbl_Column_Info TABLE(ID INT IDENTITY(1, 1),ColName VARCHAR(150))
INSERT INTO @Tbl_Column_Info(ColName)
SELECT REPLACE(c.Name, '-1', '')
FROM sys.syscolumns c JOIN sys.sysobjects o ON c.id = o.id
WHERE o.Name = @TargetTableName AND SUBSTRING(c.Name, PATINDEX('%-%',c.Name), 2)='-1'
/*
loop through the table variable @Tbl_Column_Info to create parameter definition string @ParamDefinitions
and a parameter list for all columns @CursorParameterList to be used in a SQL cursor later.
buld cursor's select statement @CursorSelectStatement and update statement @UpdateSql
Parameters are all declared as varchar(250) which are long enough for every column
*/
DECLARE @ParamDefinitions VARCHAR(1000),@CursorParameterList VARCHAR(500)
,@CursorSelectStatement VARCHAR(4000) ,@UpdateSql VARCHAR(8000)
SELECT @ParamDefinitions='', @CursorParameterList='', @CursorSelectStatement='select '
SELECT @UpdateSql='update ' + @TargetTableName + ' set ' + CHAR(9)
--loop through @Tbl_Column_Info
DECLARE @ColumnName VARCHAR(100), @NextRowID INT, @CurrentRowID INT
SELECT @NextRowID=0, @CurrentRowID=0
SELECT TOP 1 @NextRowID = ID, @ColumnName = ColName FROM @Tbl_Column_Info ORDER BY ID
WHILE @NextRowID IS NOT NULL
BEGIN
SELECT @CurrentRowID = @NextRowID
SELECT @ParamDefinitions=@ParamDefinitions + '@p' + CONVERT(varchar, @CurrentRowID) + ' varchar(250), ';
SELECT @CursorParameterList=@CursorParameterList + '@p' + CONVERT(varchar, @CurrentRowID) + ', ';
SELECT @CursorSelectStatement = @CursorSelectStatement + '[' + @ColumnName + '],'
SELECT @UpdateSql = @UpdateSql + '[' + @ColumnName + '-0]=@p' + CONVERT(varchar(5), @CurrentRowID) + ', '
SELECT @NextRowID=NULL --reset @NextRowID
SELECT TOP 1 @NextRowID = ID, @ColumnName = colName FROM @Tbl_Column_Info
WHERE ID > @CurrentRowID ORDER BY ID
END
--upon completion, remove the trailing comma and/or add where clause as needed
SELECT @ParamDefinitions=SUBSTRING(@ParamDefinitions, 1, LEN(@ParamDefinitions)-1)
SELECT @CursorParameterList=SUBSTRING(@CursorParameterList, 1, LEN(@CursorParameterList)-1)
SELECT @CursorSelectStatement=SUBSTRING(@CursorSelectStatement, 1, LEN(@CursorSelectStatement)-1) + CHAR(10) + 'from ##GlobalTempTable' + CHAR(10)
SELECT @UpdateSql=LEFT(@UpdateSql, LEN(@UpdateSql)-1) + ' Where EmployeeID=' + CONVERT(VARCHAR, @EmployeeID)
--construct a SQL cursor statement that execute a system SQL proc: sp_ExecuteSql
DECLARE @CursorSql VARCHAR(8000)
SELECT @CursorSql='Declare ' + @ParamDefinitions + CHAR(10)
SELECT @CursorSql=@CursorSql + 'Declare @Counter INT, @UpdateRevised nvarchar(4000), @Params nvarchar(4000) ' + CHAR(10)
SELECT @CursorSql=@CursorSql + 'select @Counter=0, @params=' + QUOTENAME(@ParamDefinitions, CHAR(39)) +''+ CHAR(10)
SELECT @CursorSql=@CursorSql + 'Declare DataCursor CURSOR FOR ' + CHAR(10) + @CursorSelectStatement + CHAR(10)
SELECT @CursorSql=@CursorSql + 'Open DataCursor;' + CHAR(10) + 'Fetch next from DataCursor into ' + @CursorParameterList + CHAR(10)
SELECT @CursorSql=@CursorSql + 'While @@Fetch_Status=0 ' + CHAR(10) + 'Begin ' + CHAR(10)
SELECT @CursorSql=@CursorSql + CHAR(9) + 'select @Counter=@Counter + 1 ' + CHAR(10)
SELECT @CursorSql=@CursorSql + CHAR(9) + 'select @UpdateRevised=REPLACE(''' + @UpdateSql + ''', ''-0'', ''-'' + CONVERT(VARCHAR, @Counter))'+ CHAR(10)
SELECT @CursorSql=@CursorSql + CHAR(9) + 'Execute sp_ExecuteSql @UpdateRevised, @params, ' + @CursorParameterList + CHAR(10)
SELECT @CursorSql=@CursorSql + CHAR(9) + 'Fetch next from DataCursor into ' + @CursorParameterList + CHAR(10)
SELECT @CursorSql=@CursorSql + 'End ' + CHAR(10) + 'Close DataCursor' + CHAR(10) + 'Deallocate DataCursor' + CHAR(10)
--PRINT @CursorSql
EXEC(@CursorSql)
DROP TABLE ##GlobalTempTable
END
这是一个相对较长的存储过程，包含大量的字符串连接。让我们详细看看它是如何工作的。传递了两个参数：`@EmployeeID` - 用于检索员工数据，和 `@TargetTableName` - 目标表名。
首先，从目标表中删除上次执行创建的记录，然后插入一个新记录，其中包含传入的 `@EmployeeID` 值，其余列默认为 NULL。请参阅清单 8。
列表 8
--remove the record created in a previous execution from the target table
DECLARE @SqlTemp VARCHAR(1000)
SELECT @SqlTemp='delete from ' + @TargetTableName + ' where EmployeeID=' + CONVERT(VARCHAR, @EmployeeID) + CHAR(10)
--insert a record into the target table with only EmployeeID (other fields default to null)
SELECT @SqlTemp=@SqlTemp + 'insert into ' + @TargetTableName + '(EmployeeID) values(' + CONVERT(VARCHAR, @EmployeeID) + ')'
EXECUTE(@SqlTemp)
其次，检索此员工的相关记录并将其保存到 `##GlobalTempTable` 中，如清单 9 所示。数据检索通过与特定目标表相关的存储过程完成。对于 Trans_Education，执行 `sp_EmployeeDB_Education_Select @EmployeeID`。对于 Trans_JobHistory，执行 `sp_EmployeeDB_JobHistory_Select @EmployeeID`，对于 Trans_Employee 表，执行 `sp_EmployeeDB_Employee_Select @EmployeeID`。
需要注意的是，在此演示中，数据仅从源表 Education、`JobHistory` 或 `Employee` 中选择。然而，在实际的业务应用程序中，数据可能来自各种表、视图、函数和存储过程，具有复杂的连接和条件，只要返回的列与目标表中的列匹配即可。
列表 9
--place all records retured into the ##GlobalTempTable
IF @TargetTableName='Trans_Employee'
BEGIN
EXEC sp_EmployeeDB_Employee_Select @EmployeeID
END
IF @TargetTableName='Trans_Education'
BEGIN
EXEC sp_EmployeeDB_Education_Select @EmployeeID
END
IF @TargetTableName='Trans_JobHistory'
BEGIN
EXEC sp_EmployeeDB_JobHistory_Select @EmployeeID
END
--if more tables are required, add similar statement as the above
第三，利用系统表 `syscolumns` 和 `sysobjects`，将目标表的列提取到一个表变量 `Tbl_Column_Info` 中，如清单 10 所示。只提取带有尾随数字“-1”的第一组列名。在提取过程中会移除尾随数字，以便列名与相关源表中的列名相同，并将动态添加适当的尾随数字。
列表 10
--pull the firs set of column names (ending with '-1') into a table variable.
DECLARE @Tbl_Column_Info TABLE(ID INT IDENTITY(1, 1),ColName VARCHAR(150))
INSERT INTO @Tbl_Column_Info(ColName)
SELECT REPLACE(c.Name, '-1', '')
FROM sys.syscolumns c JOIN sys.sysobjects o ON c.id = o.id
WHERE o.Name = @TargetTableName AND SUBSTRING(c.Name, PATINDEX('%-%',c.Name), 2)='-1'
第四，循环遍历表变量 `Tbl_Column_Info`，创建参数定义字符串：`@ParamDefinitions`，参数列表：`@CursorParameterList`，并构建 SQL 游标的 SELECT 语句：`@CursorSelectStatement`，并构建 UPDATE 语句：`@UpdateSql`。为简单起见，所有参数都声明为 varchar(250)，这对于所有涉及的列来说都足够长。参数声明基于 SQL 系统存储过程 `sp_ExecuteSql` 的要求，如前所述。此工作通过清单 11 中的 SQL 脚本完成。
列表 11
DECLARE @ParamDefinitions VARCHAR(1000),@CursorParameterList VARCHAR(500)
,@CursorSelectStatement VARCHAR(4000) ,@UpdateSql VARCHAR(8000)
SELECT @ParamDefinitions='', @CursorParameterList='', @CursorSelectStatement='select '
SELECT @UpdateSql='update ' + @TargetTableName + ' set ' + CHAR(9)
--loop through @Tbl_Column_Info
DECLARE @ColumnName VARCHAR(100), @NextRowID INT, @CurrentRowID INT
SELECT @NextRowID=0, @CurrentRowID=0
SELECT TOP 1 @NextRowID = ID, @ColumnName = ColName FROM @Tbl_Column_Info ORDER BY ID
WHILE @NextRowID IS NOT NULL
BEGIN
SELECT @CurrentRowID = @NextRowID
SELECT @ParamDefinitions=@ParamDefinitions + '@p' + CONVERT(varchar, @CurrentRowID) + ' varchar(250), ';
SELECT @CursorParameterList=@CursorParameterList + '@p' + CONVERT(varchar, @CurrentRowID) + ', ';
SELECT @CursorSelectStatement = @CursorSelectStatement + '[' + @ColumnName + '],'
SELECT @UpdateSql = @UpdateSql + '[' + @ColumnName + '-0]=@p' + CONVERT(varchar(5), @CurrentRowID) + ', '
SELECT @NextRowID=NULL --reset @NextRowID
SELECT TOP 1 @NextRowID = ID, @ColumnName = colName FROM @Tbl_Column_Info
WHERE ID > @CurrentRowID ORDER BY ID
END
--upon completion, remove the trailing comma and/or add where clause as needed
SELECT @ParamDefinitions=SUBSTRING(@ParamDefinitions, 1, LEN(@ParamDefinitions)-1)
SELECT @CursorParameterList=SUBSTRING(@CursorParameterList, 1, LEN(@CursorParameterList)-1)
SELECT @CursorSelectStatement=SUBSTRING(@CursorSelectStatement, 1, LEN(@CursorSelectStatement)-1) + CHAR(10) + 'from ##GlobalTempTable' + CHAR(10)
SELECT @UpdateSql=LEFT(@UpdateSql, LEN(@UpdateSql)-1) + ' Where EmployeeID=' + CONVERT(VARCHAR, @EmployeeID)
最后，清单 12 将所有内容整合在一起，创建 SQL 游标并执行动态构建的 SQL 脚本，将保存在 `##GlobalTempTable` 中的行转换为目标表中的列。上一节中的清单 3 是当传入 `@EmployeeID`=3 和 `@TargetTableName`='Trans_Education' 时，由存储过程生成的示例脚本。
列表 12
--construct a sql cursor statement that execute a system SQL stored procedure: sp_ExecuteSql()
DECLARE @CursorSql VARCHAR(8000)
SELECT @CursorSql='Declare ' + @ParamDefinitions + CHAR(10)
SELECT @CursorSql=@CursorSql + 'Declare @Counter INT, @UpdateRevised nvarchar(4000), @Params nvarchar(4000) ' + CHAR(10)
SELECT @CursorSql=@CursorSql + 'select @Counter=0, @params=' + QUOTENAME(@ParamDefinitions, CHAR(39)) +''+ CHAR(10)
SELECT @CursorSql=@CursorSql + 'Declare DataCursor CURSOR FOR ' + CHAR(10) + @CursorSelectStatement + CHAR(10)
SELECT @CursorSql=@CursorSql + 'Open DataCursor;' + CHAR(10) + 'Fetch next from DataCursor into ' + @CursorParameterList + CHAR(10)
SELECT @CursorSql=@CursorSql + 'While @@Fetch_Status=0 ' + CHAR(10) + 'Begin ' + CHAR(10)
SELECT @CursorSql=@CursorSql + CHAR(9) + 'select @Counter=@Counter + 1 ' + CHAR(10)
SELECT @CursorSql=@CursorSql + CHAR(9) + 'select @UpdateRevised=REPLACE(''' + @UpdateSql + ''', ''-0'', ''-'' + CONVERT(VARCHAR, @Counter))'+ CHAR(10)
SELECT @CursorSql=@CursorSql + CHAR(9) + 'Execute sp_ExecuteSql @UpdateRevised, @params, ' + @CursorParameterList + CHAR(10)
SELECT @CursorSql=@CursorSql + CHAR(9) + 'Fetch next from DataCursor into ' + @CursorParameterList + CHAR(10)
SELECT @CursorSql=@CursorSql + 'End ' + CHAR(10) + 'Close DataCursor' + CHAR(10) + 'Deallocate DataCursor' + CHAR(10)
--PRINT @CursorSql
EXEC(@CursorSql)
DROP TABLE ##GlobalTempTable
这就是数据转换的 SQL 语句如何动态构建和执行的。要处理特定员工的所有目标表，可以多次运行该过程，每次运行都传递不同的目标表名，如清单 13 所示。
列表 13
-- =============================================
-- Description: Call a stored proc to process data from rows to column for each result table
-- =============================================
CREATE PROCEDURE [dbo].[sp_EmployeeDB_DataTransformation_Main]
@EmployeeID int
AS
BEGIN
SET NOCOUNT ON
EXEC sp_EmployeeDB_DataTransformation_Data_Processing @EmployeeID, 'Trans_Employee'
EXEC sp_EmployeeDB_DataTransformation_Data_Processing @EmployeeID, 'Trans_Education'
EXEC sp_EmployeeDB_DataTransformation_Data_Processing @EmployeeID, 'Trans_JobHistory'
END
转换后的数据可以使用 `Trans_Employee` 表左连接 `Trans_Education` 和 `Trans_JobHistory` 在 SQL 视图或存储过程中呈现，类似于下面的清单 14。
清单 14
SELECT emp.[EmployeeID] ,
[FirstName-1] ,
[LastName-1] ,
[PhoneNumber-1] ,
[Fax-1] ,
[Email-1] ,
[DateHired-1] ,
[SchoolName-1] ,
[Degree-1] ,
[YearOfGraduation-1] ,
[SchoolName-2] ,
[Degree-2] ,
[YearOfGraduation-2] ,
[SchoolName-3] ,
[Degree-3] ,
[YearOfGraduation-3] ,
[Company-1] ,
[Position-1] ,
[Class-1] ,
[Supervisor-1] ,
[TerminationDate-1] ,
[Company-2] ,
[Position-2] ,
[Class-2] ,
[Supervisor-2] ,
[TerminationDate-2] ,
[Company-3] ,
[Position-3] ,
[Class-3] ,
[Supervisor-3] ,
[TerminationDate-3]
FROM Trans_Employee emp
LEFT JOIN Trans_Education edu ON emp.EmployeeID = edu.EmployeeID
LEFT JOIN Trans_JobHistory his ON his.EmployeeID = emp.EmployeeID
WHERE emp.EmployeeID = @EmployeeID
下载
下载内容包括一个用于整个示例数据库 EmployeeDB 的 SQL 脚本文件和一个 ASP.NET 网站项目。
在 SSMS 中打开 SQL 脚本文件。确保脚本开头指定的数据库文件夹路径在您的本地机器上存在。如果不存在，请创建一个或修改脚本以指向您机器上的不同文件夹。创建 EmployeeDB 数据库后，执行存储过程 `sp_EmployeeDB_DataTransformation_Main @EmployeeID` 以转换数据，并执行 `sp_EmployeeDB_DataTransformation_Result_Select @EmployeeID` 以查看结果。
SQL 版本为：Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.1
ASP.NET 网站项目 DynamicSql 提供了另一种执行存储过程和查看结果的方式。数据库 EmployeeDB 位于 App_Data 文件夹中。将网站项目加载到 Visual Studio 中。右键单击 default.aspx 并从联系人菜单中选择“在浏览器中查看”以运行应用程序。
演示中的 Visual Studio 版本是：Visual Studio 2012, Update 1。
进一步研究
演示中已应用简化。例如，目标表中的所有列都只使用 varchar 数据类型。此外，`sp_ExecuteSql` 执行中涉及的参数都声明为 varchar(250)。这些简化使得构建 SQL 语句的过程更容易，并且在许多业务案例中是可以接受的。然而，有时可能严格要求不同的数据类型。在此领域还需要做进一步的工作。
另一个可能的问题是 SQL 脚本无法处理多个用户的并发执行。由于使用了全局临时表的单一名称 - `##GlobalTempTable`，并发执行期间可能会发生冲突。为了避免这个问题，一个可能的修改是将登录用户名附加到全局临时表名称的末尾，例如 `##GlobalTempTable_[LoginUserName]`。相应地，登录用户名也应包含在目标表以及动态构建的 SQL 脚本中。
参考
MSDN: sp_ExecuteSql (事务 SQL)