問題描述
可能的重復:
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
結果是:
這篇關于SQL 查詢動態 PIVOT的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！