如何创建动态(列数可能会更改)SQL Server中存储过程内的临时表

问题描述：

大家好，

我的要求是创建动态(列数可能会变化)sql server中存储过程中的临时表.在我的临时表中，4列是静态的(将来永远不会改变)其余的是动态的(将来可能会改变).

四个静态列如下:
CVRelID，CustomerID，VendorID和TransactionID

以下是从表tblSupportDocuments获取动态列的查询:

Hi all,

My requirement is to create dymanic(no.of columns may change) Temporary table inside stored procedure in sql server.In my temporary table 4 columns are static(never change in future) remaining are dynamic(may change in future).

four static columns are as follows:
CVRelID,CustomerID,VendorID and TransactionID

The following is query to get dynamic columns form table tblSupportDocuments:

SELECT SDID,SDName,SDIsActive FROM tblSupportDocuments WHERE SDIsActive=1

动态列名称应作为表tblSupportDocument中的SDName值.
例如:SuppDoct1，SuppDoct2 ......，SuppDoct3等....

请有人给我一些示例代码来满足我的要求.

在此先感谢

Uday

Dynamic column names should be as SDName values from table tblSupportDocument.
for example: SuppDoct1,SuppDoct2......,SuppDoct3 etc....

please anybody give me some sample code to acheive my requirement.

Thanks in advance

Uday

答

您要搜索"t-SQL动态SQL".

You want to google "t-SQL dynamic SQL".

您的问题可以通过使用数据透视表解决sql
搜索SQL Server数据透视查询

Your problem can be solved using pivot table in sql
search for sql server pivot query

这是创建动态列的方法.
因为我没有您的表结构，所以我无法帮助您如何填充临时表.

This is how you create dynamic columns.
Because I don''t have your table structure I cannot help you how to fill the temp table.

IF OBJECT_ID( 'tempdb..#temp') IS NOT NULL
    BEGIN
    DROP TABLE #temp
    END
CREATE TABLE #temp
    (
    [CVRelID]       INT,
    [CustomerID]    INT,
    [VendorID]      INT,
    [TransactionID] INT
    )

DECLARE @script VARCHAR(8000)
SET     @script = 'ALTER TABLE #temp ADD '

SELECT  @script = @script + QUOTENAME(SDName) + ' VARCHAR(100),'
FROM    tblSupportDocuments
WHERE   SDIsActive = 1


SET     @script = SUBSTRING( @script, 1, LEN(@script) - 1)-- remove last ","
PRINT   @script
EXEC    (@script)

如何创建动态(列数可能会更改)SQL Server中存储过程内的临时表

相关推荐