Getting a Dynamically-Generated Pivot-Table into a

问题:

I've seen this, so I know how to create a pivot table with a dynamically generated set of fields. My problem now is that I'd like to get the results into a temporary table.

I know that in order to get the result set into a temp table from an EXEC statement you need to predefine the temp table. In the case of a dynamically generated pivot table, there is no way to know the fields beforehand.

The only way I can think of to get this type of functionality is to create a permanent table using dynamic SQL. Is there a better way?

回答1:

you could do this:

-- add 'loopback' linkedserver 
if exists (select * from master..sysservers where srvname = 'loopback')
    exec sp_dropserver 'loopback'
go
exec sp_addlinkedserver @server = N'loopback',
    @srvproduct = N'',
    @provider = N'SQLOLEDB', 
    @datasrc = @@servername
go

declare @myDynamicSQL varchar(max)
select @myDynamicSQL = 'exec sp_who'
exec('
    select * into #t from openquery(loopback, ''' + @myDynamicSQL + ''');
    select * from #t
    ')

EDIT: addded dynamic sql to accept params to openquery

回答2:

Ran in to this issue today, and posted on my blog. Short description of solution, is to create a temporary table with one column, and then ALTER it dynamically using sp_executesql. Then you can insert the results of the dynamic PIVOT into it. Working example below.

CREATE TABLE #Manufacturers
(
    ManufacturerID INT PRIMARY KEY,
    Name VARCHAR(128)
)

INSERT INTO #Manufacturers (ManufacturerID, Name)
VALUES (1,'Dell')
INSERT INTO #Manufacturers (ManufacturerID, Name)
VALUES (2,'Lenovo')
INSERT INTO #Manufacturers (ManufacturerID, Name)
VALUES (3,'HP')

CREATE TABLE #Years
(YearID INT, Description VARCHAR(128))
GO

INSERT INTO #Years (YearID, Description) VALUES (1, '2014')
INSERT INTO #Years (YearID, Description) VALUES (2, '2015')
GO

CREATE TABLE #Sales
(ManufacturerID INT, YearID INT,Revenue MONEY)
GO

INSERT INTO #Sales (ManufacturerID, YearID, Revenue) VALUES(1,2,59000000000)
INSERT INTO #Sales (ManufacturerID, YearID, Revenue) VALUES(2,2,46000000000)
INSERT INTO #Sales (ManufacturerID, YearID, Revenue) VALUES(3,2,111500000000)
INSERT INTO #Sales (ManufacturerID, YearID, Revenue) VALUES(1,1,55000000000)
INSERT INTO #Sales (ManufacturerID, YearID, Revenue) VALUES(2,1,42000000000)
INSERT INTO #Sales (ManufacturerID, YearID, Revenue) VALUES(3,1,101500000000)
GO

DECLARE @SQL AS NVARCHAR(MAX)
DECLARE @PivotColumnName AS NVARCHAR(MAX)
DECLARE @TempTableColumnName AS NVARCHAR(MAX)
DECLARE @AlterTempTable AS NVARCHAR(MAX)

--get delimited column names for various SQL statements below
SELECT 
    -- column names for pivot
    @PivotColumnName= ISNULL(@PivotColumnName + N',',N'') + QUOTENAME(CONVERT(NVARCHAR(10),YearID)),
    -- column names for insert into temp table
    @TempTableColumnName = ISNULL(@TempTableColumnName + N',',N'') + QUOTENAME('Y' + CONVERT(NVARCHAR(10),YearID)),
    -- column names for alteration of temp table
    @AlterTempTable = ISNULL(@AlterTempTable + N',',N'') + QUOTENAME('Y' + CONVERT(NVARCHAR(10),YearID)) + ' MONEY' 
FROM (SELECT DISTINCT [YearID] FROM #Sales) AS Sales

CREATE TABLE #Pivot
(
     ManufacturerID INT
)

-- Thats it! Because the following step will flesh it out.

SET @SQL = 'ALTER TABLE #Pivot ADD ' + @AlterTempTable
EXEC sp_executesql @SQL

--execute the dynamic PIVOT query into the temp table 
SET @SQL =  N'
    INSERT INTO #Pivot (ManufacturerID, ' + @TempTableColumnName + ')
    SELECT ManufacturerID, ' + @PivotColumnName + '
    FROM #Sales S
    PIVOT(SUM(Revenue) 
      FOR S.YearID IN (' + @PivotColumnName + ')) AS PivotTable'
EXEC sp_executesql @SQL

SELECT M.Name, P.*
FROM #Manufacturers M
INNER JOIN #Pivot P ON M.ManufacturerID = P.ManufacturerID

回答3:

Let me try this explanation of select into instead. I'm running SQL Server 2005 as well. Because you have PIVOT tables I'm going to assume the same or 2008.

select 
    o.*,
    OtherField1,
    OtherField2
INTO #temp
FROM
    OriginalOtherData as ood
PIVOT (
    MAX([Value])
    FOR Field in (OtherField1, OtherField2)
) as piv
RIGHT OUTER join
    Original o on o.OriginalSD = piv.OriginalSD

select * from #temp
Drop table #temp

The only difference between a normal select and a select into is that INTO #table part.

回答4:

• for query (select col1, col2, col3 from tablename
• col1 becomes rowlabels
• col2 becomes columnheaders

• col3 is the dataset

• also gets rid of the global table

  if OBJECT_ID('tempdb..#3') is not null drop table #3
  if OBJECT_ID('tempdb..##3') is not null drop table ##3
  DECLARE @cols AS NVARCHAR(MAX),
      @query  AS NVARCHAR(MAX)
  select @cols = STUFF((SELECT distinct ',' + QUOTENAME(   col2    ) from    tablename    FOR XML PATH(''), col2).value('.', 'NVARCHAR(MAX)'),1,1,'')
  set @query = 'SELECT col1, ' + @cols + ' into ##3 from ( select col1, col2, col3 from tablename ) x  pivot (  max(col3)for col2 in (' + @cols + ')) p '
  execute(@query)   
  select * into #3 from ##3 if OBJECT_ID('tempdb..##3') -- is not null drop table ##3