如何在不使用Cursor或Pivot的情况下将垂直数据动态转换为水平数据

Question

目前我正试图通过在存储过程中实现一个游标来动态地将垂直数据结构转换为水平数据。但是，执行时间太长。我正试图找到一种优化这种方法的方法。除了Cursor之外，还有什么更好的使用方法？注意：由于数据映射分散，因此无法使用PIVOT。这是我当前的代码，包含样本数据和预期输出：

到目前为止，我所做的是：有三个临时表#tablestructure、#raw和#dsid#Tablestructure包含destfieldname下创建表所需的所有列。然后，一旦创建了它，我们需要从#raw中获取内容，并将其插入到基于行号和列号创建的新表中

#表结构

int

desttablename	destfieldname	数据类型
*	主题编号
样本	人物角色	nvarchar(20(
样本	人员位置	nvarchar(20(

老实说，这个问题不清楚，我猜你在追求什么，因为它从来没有真正解释过。这将创建两个表，*和sample(在硬编码模式dbo上(，INSERT将表raw中的数据放入相应的表中。它还假设您使用的是完全受支持的SQL Server版本。

然而，这完全是一团糟：

SELECT *
INTO dbo.tablestructure
FROM (VALUES(N'*',N'subjectnumber',N'int'),
(N'sample',N'personname',N'nvarchar(20)'),
(N'sample',N'personlocation',N'nvarchar(20)'))V(desttablename,destfieldname,datatype);
SELECT *
INTO dbo.raw
FROM (VALUES(1,1,N'subjectnumber',N'132516352'),
(1,2,N'personname',N'Alex'),
(2,1,N'subjectnumber',N'132516353'),
(2,3,N'personlocation',N'Canada'),
(1,3,N'personlocation',N'Australia'),
(2,2,N'personname',N'John'))V(rownumber,columnnumber,fieldname,contents);
GO
DECLARE @SQL nvarchar(MAX),
@CRLF nchar(2) = NCHAR(13) + NCHAR(10);
WITH Tables AS(
SELECT ts.desttablename,
N'CREATE TABLE dbo.' + QUOTENAME(ts.desttablename) + N' (' + STRING_AGG(QUOTENAME(ts.destfieldname) + N' ' + QUOTENAME(V.datatype) + V.precisionscale,',') WITHIN GROUP (ORDER BY ts.destfieldname) + N');' AS CreateStatement
FROM dbo.tablestructure ts
--I don't trust data in a table like this, so I'm going to check it's "good"
--This won't cause an error it's it's bad, but it'll always avoid injection
--If you want an error, you would need to checl the validity first
CROSS APPLY (VALUES(LEFT(ts.datatype,CHARINDEX('(',datatype+'(')-1),STUFF(ts.datatype,1,CHARINDEX('(',datatype+'(')-1,'')))V(datatype,precisionscale)
JOIN sys.types t ON V.datatype = t.name --Make sure that the datatype is valid
WHERE V.precisionscale NOT LIKE '%[^(),0-9 ]%' --Only contains parenthesis, numbers, commas and spaces
OR V.precisionscale = '(MAX)' --Or the scale is MAX
GROUP BY ts.desttablename)
SELECT @SQL = STRING_AGG(CreateStatement,@CRLF) 
FROM Tables T;
EXEC sys.sp_executesql @SQL; 
GO
DECLARE @SQL nvarchar(MAX),
@CRLF nchar(2) = NCHAR(13) + NCHAR(10);
WITH InsertClause AS (
SELECT ts.desttablename,
N'INSERT INTO dbo.' + QUOTENAME(desttablename) + N' (' + STRING_AGG(QUOTENAME(destfieldname) + N'',',') WITHIN GROUP (ORDER BY destfieldname) + N')' AS InsertClause
FROM dbo.tablestructure ts
WHERE EXISTS (SELECT 1
FROM dbo.raw r
WHERE ts.destfieldname = r.fieldname)
GROUP BY ts.desttablename),
RowValues AS(
SELECT IC.InsertClause,
N'(' + STRING_AGG(N'N''' + REPLACE(r.contents,'''','''''') + N'''',',') WITHIN GROUP (ORDER BY r.fieldname) + N')' AS RowInsert
FROM InsertClause IC
JOIN dbo.tablestructure ts ON IC.desttablename = ts.desttablename
JOIN dbo.raw r ON ts.destfieldname = r.fieldname
GROUP BY IC.InsertClause,
r.rownumber),
InsertStatements AS(
SELECT InsertClause + @CRLF +
N'VALUES ' + STRING_AGG(RowInsert,N',') + N';' AS FullInsertStatement
FROM RowValues
GROUP BY InsertClause)
SELECT @SQL = STRING_AGG(FullInsertStatement,@CRLF)
FROM InsertStatements;
PRINT @SQL;
EXEC sys.sp_executesql @SQL;
GO
SELECT *
FROM dbo.[*];
SELECT *
FROm dbo.[sample];
GO
DROP TABLE dbo.tablestructure;
DROP TABLe dbo.raw;
GO
DROP TABLE IF EXISTS dbo.[*]
DROP TABLE IF EXISTS dbo.sample;

db<gt；小提琴