SQL 查询动态 PIVOT
可能的重复:
SQL Server 动态 PIVOT 查询?
我有以下结构的临时表:
I have temporary table with following structure:
MONTH ID CNT
----- ----------- ---
4 TOTAL_COUNT 214
5 TOTAL_COUNT 23
6 TOTAL_COUNT 23
4 FUNC_COUNT 47
5 FUNC_COUNT 5
6 FUNC_COUNT 5
4 INDIL_COUNT 167
5 INDIL_COUNT 18
6 INDIL_COUNT 18
我如何在此表中获得超过一个月的数据透视表，例如:
How i can get the Pivot over month in this table like:
ID APRIL MAY JUNE
----------- ----- --- ----
TOTAL_COUNT 214 23 23
FUNC_COUNT 47 5 5
INDIL_COUNT 167 18 18
请考虑这种表格格式.我在发布这种格式时有点乱.
Please consider this table format. I am little messy in posting this format.
推荐答案
虽然您可以使用 Static Pivot - 您可以对月份进行硬编码.在评论中，您表示月数可能未知，如果是这种情况，那么您将需要使用 Dynamic Pivot 来生成月列表.使用 Dynamic Pivot 可让您灵活地在运行之前不知道所需的列.
While you can use a Static Pivot - one that you hard-code the months. In the comments, you stated that the number of months maybe be unknown, if that is the case then you will want to use a Dynamic Pivot to generate the list of months. Using a Dynamic Pivot gives you the flexibility of not knowing the columns you need until you run it.
create table t
(
[month] int,
[id] nvarchar(20),
[cnt] int
)
insert t values (4,'TOTAL_COUNT',214)
insert t values (5,'TOTAL_COUNT',23)
insert t values (6,'TOTAL_COUNT',23)
insert t values (4,'FUNC_COUNT',47)
insert t values (5,'FUNC_COUNT',5)
insert t values (6,'FUNC_COUNT',5)
insert t values (4,'INDIL_COUNT',167)
insert t values (5,'INDIL_COUNT',18)
insert t values (6,'INDIL_COUNT',18)
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(month)
FROM t
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT id, ' + @cols + ' from
(
select month, id, cnt
from t
) x
pivot
(
sum(cnt)
for month in (' + @cols + ')
) p '
execute(@query)
drop table t
结果是:
相关文章