SQL Server 中的动态透视多列

我有一张这样的桌子

Id   Name   FromAddress   ToAddress
1    Joey      ABC          JKL
2    Joey      DEF          MNP
3    Joey      GHI          OQR

我正在 SQL Server 中寻找带有动态数据透视表的以下输出

Name   FromAdrs1   FromAdrs2   FromAdrs3   ToAdrs1   ToAdrs2   ToAdrs3
Joey     ABC         DEF         GHI          JKL      MNP       OQR

注意：行数根据 Id 值而变化，因此我尝试使用动态数据透视获取输出。

这是我尝试过的代码，看起来是正确的，但给我一个错误。

IF OBJECT_ID('temp..#temp') IS NOT NULL
DROP TABLE #temp

CREATE TABLE #temp
(
    Id INT, Name VARCHAR(10), FromAddress VARCHAR(10), ToAddress VARCHAR(10)
)

INSERT INTO #temp VALUES (1, 'Joey', 'ABC', 'JKL'), (2, 'Joey', 'DEF', 'MNP'), (3, 'Joey', 'GHI', 'OQR')

--SELECT * FROM #temp


DECLARE @colsFrom AS NVARCHAR(MAX),
@colsTo AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX);

SET @colsFrom = STUFF((SELECT distinct ',' + QUOTENAME(CONVERT(VARCHAR(2), t.id) + 'From') 
        FROM #temp t
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

SET @colsTo = STUFF((SELECT distinct ',' + QUOTENAME(CONVERT(VARCHAR(2), t.id) + 'To') 
        FROM #temp t
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

set @query = 'SELECT *, ' + @colsFrom + ', ' + @colsTo + ' from 
        (
            select *
            from #temp
       ) T
        PIVOT 
        (
            max(FromAddress)
            for Id in (REPLACE('''+@colsFrom+''',''From'',''''))
        ) p
        PIVOT 
        (
            max(ToAddress)
            for Id in (REPLACE('''+@colsTo+''',''To'',''''))
        ) Q'

execute(@query)

DROP TABLE #temp

任何帮助表示赞赏。感谢大家抽出宝贵的时间。

编辑：这是错误

我相信问题是IN ()表达于PIVOTs。列列表明确必须是字段名称列表，而不是函数，也不是 varchar 文字或函数值的列表。你有一个REPLACE()在那里发挥作用。引擎期望寻找一个名为[REPLACE]然后对出现的左括号感到困惑。

这是有效的（方括号表示强调）：

SELECT VendorID, Employee, Orders
FROM 
   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
   FROM pvt) p
UNPIVOT
   (Orders FOR Employee IN 
      ([Emp1], [Emp2], [Emp3], [Emp4], [Emp5])
)AS unpvt;

这不是：

SELECT VendorID, Employee, Orders
FROM 
   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
   FROM pvt) p
UNPIVOT
   (Orders FOR Employee IN 
      ('Emp1', 'Emp2', 'Emp3', 'Emp4', 'Emp5')
)AS unpvt;

这是无效的：

SELECT VendorID, Employee, Orders
FROM 
   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
   FROM pvt) p
UNPIVOT
   (Orders FOR Employee IN 
      (REPLACE('Emp1','1','A'), REPLACE('Emp2','2','B'))
)AS unpvt;

更换execute(@query) with a select @query or print @query查看代码生成的查询并以这种方式对查询分析器中的语法进行故障排除。然后向后工作。

你想做REPLACE()在您构建查询的同一级别。最终的查询@query变量应该已经固定了列名。

或者，您可以生成@colsFromLabels, @colsToLabels, @colsFrom and @colsTo与前两者有'from' and to添加位，后两个只是列名。

就方括号转义而言，您想要的输出也有点粗糙。