tsql - 如何使用表变量进行动态透视？

Question

我有一个名为:@table2 的表变量，其中包含...

col1 -- col2id   -- 101name -- Bananaage  -- 20id   -- 102name -- Puddingage  -- 21

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(@trial2.col1)             FROM @trial2             FOR XML PATH(''), TYPE            ).value('.', 'NVARCHAR(MAX)')         ,1,1,'')
set @query = 'SELECT *, ' + @cols + ' from             (                select *                from @trial2           ) x            pivot             (                max(col2)                for col1 in (' + @cols + ')            ) p             ORDER BY p.s'execute(@query)

我有这个错误:“必须声明标量变量“@trial2”，在行“select @cols = STUFF((SELECT distinct ',' + QUOTENAME(@trial2.col1) “
但是当我将“@trial2.col1”更改为“col1”时，另一个错误提示:“必须声明表变量“@trial2”，你能帮我做什么吗？

Answer 1

查询很可能运行良好，直到您执行生成的 SQL。此查询的结果:

DECLARE @cols AS NVARCHAR(MAX)
DECLARE @query AS NVARCHAR(MAX)
DECLARE @trial2 table( col1 varchar(100) NOT NULL , col2 varchar(100) NOT NULL )

INSERT INTO @trial2    ([col1], [col2])
VALUES     ('id', '101'),('name', 'Banana'),('age', '20'),('id', '102'),('name', 'Pudding'),('age', '21')

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(t.col1) 
            FROM @trial2 t
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query = 'SELECT *, ' + @cols + ' from 
            (
                select *
                from @trial2 t
           ) x
            pivot 
            (
                max(col2)
                for col1 in (' + @cols + ')
            ) p 
            ORDER BY p.s'

select(@query)

是:

SELECT *, [age],[id],[name] from ( select * from @trial2 t ) x pivot ( max(col2) for col1 in ([age],[id],[name]) ) p ORDER BY p.s

问题是，在执行该字符串时，@trial2 不存在。我认为您应该将该表变量保存到临时表 (#trial2) 中，然后您可以期望您的动态 SQL 找到所需的表。

此外，您不应尝试在最终查询中使用 'SELECT *, ' + @cols，实际上您不应在任何生产代码中依赖 select *。

另请参阅此 sqlfiddle:http://sqlfiddle.com/#!6/68b32/592