获取在 EXEC() 中执行的动态 PIVOT 结果到一个 varchar 变量

Question

下面的代码创建了一个JSON输出。

请注意：表中#Person值是动态生成的。一旦我调用包含以下代码段的存储过程，C# 代码将输出标识为JSON即使它看起来像一个VARCHAR。我需要输出 JSON 是一个string(VARCHAR)，因为 C# 模型无法根据 #Names 返回的值动态生成。

DROP TABLE #Names
DROP TABLE #PersonInfo    
CREATE TABLE #Names (ID INT,Name VARCHAR(MAX))
CREATE TABLE #PersonInfo (ID INT,NameID INT,Subject VARCHAR(100),Marks INT)
INSERT INTO #Names VALUES (1,'Paul');
INSERT INTO #Names VALUES (2,'John');
INSERT INTO #Names VALUES (3,'Tayler');
INSERT INTO #PersonInfo VALUES (1,1,'Maths',95);
INSERT INTO #PersonInfo VALUES (2,2,'Science',32);
INSERT INTO #PersonInfo VALUES (3,3,'History',23);
INSERT INTO #PersonInfo VALUES (4,2,'Maths',32);
INSERT INTO #PersonInfo VALUES (5,3,'Science',60);
INSERT INTO #PersonInfo VALUES (6,1,'Music',60);
DECLARE @DynamicCols NVARCHAR(MAX) = '';
DECLARE @pvt NVARCHAR(MAX) = '';
DECLARE @SQLQuery NVARCHAR(MAX) = '';
SELECT @DynamicCols +=  ', SUM(' +QUOTENAME([Name])+') AS '+[Name] FROM #Names;
SET @DynamicCols = STUFF(@DynamicCols,1,1,'')
SELECT @pvt +=  ', ' +QUOTENAME([Name]) FROM #Names;
SET @pvt = STUFF(@pvt,1,1,'')
EXEC ('
SELECT [Subject],' + @DynamicCols+'
FROM (SELECT [NameID], [Subject], [Marks] FROM #PersonInfo) a
INNER JOIN #Names b ON a.NameID = b.ID
PIVOT 
(
  SUM([Marks])
  FOR [Name] IN ('+ @pvt+')
)   PIV
GROUP BY [Subject] FOR JSON AUTO, INCLUDE_NULL_VALUES');

结果

[{"Subject":"History","Paul":null,"John":null,"Tayler":23},{"Subject":"Maths","Paul":95,"John":32,"Tayler":null},{"Subject":"Music","Paul":60,"John":null,"Tayler":null},{"Subject":"Science","Paul":null,"John":32,"Tayler":60}]

DAL 层中的 C# 代码

public string GetMarks(string partyRoleIdList)
  {
   return _context.Query<string>(usp_GetMarks, new { IdList }).SingleOrDefault();
  }

有没有办法将执行的值(EXEC()(转换为VARCHAR(MAX)？

Answer 1

您可以轻松测试FOR JSON的结果是否NVARCHAR(MAX)字符串：

exec sp_describe_first_result_set N'
SELECT *
FROM
(
    VALUES(''[a'')
) DS(col)
FOR JSON AUTO
';

产生这个：

is_hidden   column_ordinal  name    is_nullable system_type_id  system_type_name    max_length  precision   scale   collation_name
0   1   JSON_F52E2B61-18A1-11d1-B105-00805F49916B   1   231 nvarchar(max)   -1  0   0   SQL_Latin1_General_CP1_CI_AS

它是字符串，因此您可以轻松地将其转换为VARCHAR(MAX)或其他内容：

EXEC ('
SELECT CAST(
(
SELECT [Subject],' + @DynamicCols+'
FROM (SELECT [NameID], [Subject], [Marks] FROM #PersonInfo) a
INNER JOIN #Names b ON a.NameID = b.ID
PIVOT 
(
  SUM([Marks])
  FOR [Name] IN ('+ @pvt+')
)   PIV
GROUP BY [Subject] FOR JSON AUTO, INCLUDE_NULL_VALUES)
 AS VARCHAR(400))');