从动态SQL结果中选择*到临时表中

debugcn 发表于 Dev

格里姆·里伯（Grim Rieber）

我编写了一个动态数据透视表，需要将结果输入到临时表中以将结果连接到另一个表。

也许有一种更好的方法可以在不插入Temp表的情况下获得我想要的结果？我可以在Dynamic Pivot内加入表以获得所需的最终结果吗？

得到错误：

Msg 263, Level 16, State 1, Line 59
Must specify table to select from.

Msg 1038, Level 15, State 5, Line 59
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

我的代码如下：

 declare @RealYear as nvarchar(max)
select @RealYear= '2016'
IF OBJECT_ID('tempdb.dbo.#TempCosts', 'U') IS NOT NULL
  DROP TABLE #TempCosts; 

IF OBJECT_ID('tempdb.dbo.#TempCostsFinal', 'U') IS NOT NULL
  DROP TABLE #TempCostsFinal; 

select * into #TempCosts from
(
--Journal Entries
select * from lth.dbo.vwJournalEntries_LTWS1
union all
--Non Journal Entries
select *,'' from lth.dbo.vwweeklydebits_ltws1
)D

--select * from #TempCosts
DECLARE @cols AS NVARCHAR(MAX)
declare  @query  AS NVARCHAR(MAX)
declare @temptable as nvarchar(max)

select @cols = STUFF((SELECT ',',  QUOTENAME(AccountRef_FullName) 
  from #TempCosts
                    group by AccountRef_FullName
                    having accountref_Fullname is not null
                    order by AccountRef_FullName
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT *,  ' + @cols + ' from 
             (
                select *
                from #TempCosts
                where year = ' + @RealYear + '
            ) x
            pivot 
            (
                sum(Amount)
                for AccountRef_FullName in (' + @cols + ')
            ) p 

        '


        execute(@query)


    select * into #TempCostsFinal Execute(@query)

约翰·卡佩莱蒂

这是有风险的，因为您有发生碰撞的风险，并且效率不高，但是您可以将动态结果放入## TempPivot

Set @query = '

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


SELECT *,' + @cols + ' 
 Into ##TempPivot
 From (
        select *
        from #TempCosts
        where year = ' + @RealYear + '
       ) x
    pivot 
    (
        sum(Amount)
        for AccountRef_FullName in (' + @cols + ')
    ) p 
'
Exec(@query)

Select * Into #Temp from ##TempPivot

Drop Table ##TempPivot

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。