更新时间:2021-11-08 00:58:12
首先你的查询的in子句是错误的。正确的做法是
First your in clause of query is wrong. Correct way of doing it is
DECLARE @TBL TABLE
(
MONTH VARCHAR(MAX),
ORDERS VARCHAR(MAX)
)
INSERT INTO @TBL VALUES('Jan','1');
INSERT INTO @TBL VALUES('Feb','2');
INSERT INTO @TBL VALUES('Mar','3');
SELECT 'Assign' Assign ,*,'Total' [Total]
from
(
SELECT * FROM @TBL
) AS A pivot(MAX(ORDERS) FOR[MONTH] IN(Jan,Feb,Mar)) AS pvt
其次,因为列本质上是动态的,因此需要动态的数据透视查询,这是非常简单。试试这些链接吧。
SQL Server动态PIVOT查询? - 堆栈溢出 [ ^ ]
SQL Server中的动态数据透视表 [ ^ ]
Sql Server中的动态PIVOT | SqlHints.com [ ^ ]
Secondly since columns are dynamic in nature hence a dynamic pivot query is required which is quite simple. Try these links for an idea.
SQL Server dynamic PIVOT query? - Stack Overflow[^]
Dynamic Pivot Query in SQL Server[^]
Dynamic PIVOT in Sql Server | SqlHints.com[^]
尝试下面的查询,因为列是动态来的
创建表#temp
(
MONTH VARCHAR(MAX),
ORDERS VARCHAR(MAX)
)
INSERT INTO #temp VALUES('Jan','1');
INSERT INTO #temp VALUES('Feb','2');
INSERT INTO #temp VALUES('Mar','3');
INSERT INTO #temp VALUES('Feb','7');
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@ cols1 AS NVARCHAR(MAX)
选择@ cols1 =(SELECT','+ QUOTENAME(MONTH)
来自#temp order by orders
FOR XML PATH(''),TYPE).value('。','NVARCHAR(MAX)')
select @ cols = STUFF(@ cols1
,1,1,'')
set @query ='SELECT'+ @cols +'
来自
(
选择MONTH,ORDERS
来自#temp
)x
pivot
(
max(ORDERS)
MONTH的
('+ @cols +')
)p'
打印@query
执行(@query)
Try below query as columns are coming dynamically
create table #temp
(
MONTH VARCHAR(MAX),
ORDERS VARCHAR(MAX)
)
INSERT INTO #temp VALUES('Jan','1');
INSERT INTO #temp VALUES('Feb','2');
INSERT INTO #temp VALUES('Mar','3');
INSERT INTO #temp VALUES('Feb','7');
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@cols1 AS NVARCHAR(MAX)
select @cols1=(SELECT ',' + QUOTENAME(MONTH)
from #temp order by orders
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
select @cols=STUFF(@cols1
,1,1,'' )
set @query = 'SELECT ' + @cols + '
from
(
select MONTH, ORDERS
from #temp
) x
pivot
(
max(ORDERS)
for MONTH in (' + @cols + ')
) p '
print @query
execute(@query)