将Pivot的结果存储到动态表中 [英] Storing the result of Pivot into a dynamic table

问题描述

if object_id('tempdb..#Table') is not null
drop table #Table

CREATE TABLE #Table (
        ID INT,
        ColumnName VARCHAR(250),
        Value VARCHAR(250)
)

INSERT INTO #Table SELECT 1,'name','Peter' 
INSERT INTO #Table SELECT 1,'phone','12345678' 
INSERT INTO #Table SELECT 1,'email','[e-mail]' 
INSERT INTO #Table SELECT 2,'name','John' 
INSERT INTO #Table SELECT 2,'phone','87654321' 
INSERT INTO #Table SELECT 2,'email','[e-mail]' 
INSERT INTO #Table SELECT 3,'name','Sarah' 
INSERT INTO #Table SELECT 3,'phone','55667788' 
INSERT INTO #Table SELECT 3,'email','[e-mail]'
insert into #table select 6,'testing','hfsdjhfdh' 
select * from #Table
---I assumed your tablename as TESTTABLE--- 
DECLARE @cols NVARCHAR(2000) 
DECLARE @query NVARCHAR(4000) 

SELECT  @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT 
                                '],[' + t.ColumnName 
                        FROM    #Table AS t 
                        --ORDER BY '],[' + t.ID 
                        FOR XML PATH('') 
                      ), 1, 2, '') + ']' 

SELECT  @cols

SET @query = 
N'SELECT ID,'+ @cols +' FROM 
(SELECT t1.ID,t1.ColumnName , t1.Value FROM #Table AS t1 ) p 
PIVOT (MAX([Value]) FOR ColumnName IN ( '+ @cols +' )) 
AS pvt;' 

EXECUTE(@query) 


DROP TABLE #Table

这是我的查询..现在我想存储'EXECUTE（的结果） @query）'进入一个应该根据pivot的结果动态生成的表

This is my query..Now i want to store the result of 'EXECUTE(@query)' into a table which should be generated dynamically according to the result of the pivot

推荐答案

看看这里：http://stackoverflow.com/questions/778079/getting-a-dynamically-generated-pivot -table-into-a-temp-table [ ^ ]



我希望它有所帮助;）

Have a look here: http://stackoverflow.com/questions/778079/getting-a-dynamically-generated-pivot-table-into-a-temp-table[^]

I hope it would help ;)

这篇关于将Pivot的结果存储到动态表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！