Geek Answers Handbook

Getting a dynamically generated pivot table into a Temp table

I saw this one , so I know how to create a pivot table with a dynamically generated set of fields. My problem is that I would like to get the results in a temporary table.

I know that in order to get the result set into the temp table from the EXEC statement, you need to predefine the temporary table. In the case of a dynamically generated pivot table, it is not possible to know the fields in advance.

The only way I can come up with this type of functionality is to create a persistent table using dynamic SQL. Is there a better way?

+4
source share
4 answers

You can do it:

-- 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: added dynamic sql to accept parameters for openquery

+1
source

Let me try this select explanation instead. I am also running SQL Server 2005. Since you have PIVOT tables, I'm going to accept 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 regular selection and selection is the INTO #table part.

+1
source

We approach this issue today and publish it on our blog. A brief description of the solution is to create a temporary table with one column, and then ALTER dynamically using sp_executesql. You can then paste the dynamic PIVOT results into it. The following is a working example.

 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
+1
source
  • for the query (select col1, col2, col3 from tablename
  • col1 becomes rowlabels
  • col2 becomes a header column

  • col3 is a data set

  • 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
0
source

More articles:


All Articles