根据查询返回的列名在Sql Server中动态生成表 [英] Dynamically generating Table in Sql Server from columns name returned from a query

问题描述

大家好，

我正在生成数据透视表，我也想在末尾显示总计"字段
所以我想动态生成一个表，但是我不会事先知道如何
它会有很多列.只有我事先知道数据类型是numeric(18,2).
现在任何人都可以帮助我解决这个问题.

Hello All,

I am generating a pivot table,I also want to show the Grand Total field at the end
So I want to generate a table dynamically but I will not know in advance as to how
many columns it would have.Only I know in advance is the datatype is numeric(18,2).
Now can anyone help me to resolve this .

ALTER proc [dbo].[GetPivotData] @measure nvarchar(200),@Week NVARCHAR(100),@country nvarchar(100)
as 
begin

DECLARE @SQL  NVARCHAR(max)
DECLARE @OpID nvarchar(100)
DECLARE @cols NVARCHAR(2000)
DECLARE @QUERY NVARCHAR(4000)

Create Table #TempTbl(Week BIGINT,Measure numeric(18,2),Operator nvarchar(100))

DECLARE @getOperators CURSOR
SET @getOperators = CURSOR FOR Select distinct(Operator)from Data 
OPEN @getOperators
FETCH NEXT
FROM @getOperators INTO @OpID
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL='SELECT TOP (8) ISNULL(Week,0) AS Week,'+@measure+' ,Operator='''+@OpID+''' FROM Data WHERE (Country ='''+@country+''') AND (Operator = '''+ @OPID +''')
 AND ( Week <'''+@Week+''') ORDER BY Week DESC'
Insert #TempTbl EXEC(@SQL)
FETCH NEXT
FROM @getOperators INTO @OpID
END
CLOSE @getOperators
DEALLOCATE @getOperators


SELECT  @cols = STUFF(( SELECT distinct '],['+Operator FROM  #TempTbl order by '],['+Operator FOR XML PATH('') ),1,2,'') + ']'
SET @QUERY='Select Week,'+@cols+' from(Select * from #TempTbl) as mytbl
pivot(sum(Measure)for Operator in ('+@cols+'))as PVTTBL Order by Week DESC'
EXEC(@QUERY)

END 

-- exec [getPivotData] '[TPDOC VOLUME]','201048','Australia'

推荐答案

运行查询时，您可能知道将要返回的列数.在这种情况下，您可以返回等于列数的其他输出参数.

否则，您可能会在此链接中找到信息
如何使用DataReader检索列架构 [

When running a query, you probably know number of columns that will be returned. If that is the case, you can return additional output parameter that will be equal to number of columns.

Otherwise, you may found information in this link
How To Retrieve Column Schema by Using the DataReader[^] helpful.

这篇关于根据查询返回的列名在Sql Server中动态生成表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！