使用PIVOT轉成每月報表，但想在每個月份底下列出細節

sql pivot

wu2960 2021-12-09 11:05:24 ‧ 896 瀏覽

分享至

--#table 原始資料
CREATE TABLE #table
(
	[month] int,
	[error] nvarchar(10),
	[area] nvarchar(10),
	[city] nvarchar(10)
)

INSERT INTO #table ([month],[error],[area],[city])
VALUES
('1', '嚴重', '北區', '台北'),
('1', '輕微', '中區', '台中'),
('3', '輕微', '南區', '高雄'),
('4', '中等', '北區', '桃園'),
('5', '輕微', '北區', '台北'),
('5', '嚴重', '北區', '桃園'),
('6', '輕微', '南區', '高雄'),
('7', '輕微', '中區', '台中'),
('7', '輕微', '中區', '台中'),
('8', '輕微', '中區', '彰化'),
('8', '嚴重', '中區', '彰化'),
('9', '中等', '北區', '新竹'),
('10', '嚴重', '南區', '台南'),
('11', '中等', '南區', '高雄')

SELECT * FROM #table ORDER BY [area],[city],[month]

--使用PIVOT
SELECT *
    FROM 
        (
          SELECT [area],[city],[month],COUNT([error]) AS error
            FROM #table
            GROUP BY [area],[city],[month]
        ) AS p
    PIVOT
        (
          COUNT([error]) FOR [month] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
        ) AS pt

CREATE TABLE #result
(
	[area] nvarchar(10),
	[city] nvarchar(10),
	[1] nvarchar(10),
	[2] nvarchar(10),
	[3] nvarchar(10),
	[4] nvarchar(10),
	[5] nvarchar(10),
	[6] nvarchar(10),
	[7] nvarchar(10),
	[8] nvarchar(10),
	[9] nvarchar(10),
	[10] nvarchar(10),
	[11] nvarchar(10),
	[12] nvarchar(10)
)
INSERT INTO #result ([area],[city],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
VALUES
('北區', '台北', '嚴重*1', '', '', '', '輕微*1', '', '', '', '', '', '', ''),
('北區', '桃園', '', '', '', '中等*1', '嚴重*1', '', '', '', '', '', '', ''),
('北區', '新竹', '', '', '', '', '', '', '', '', '中等*1', '', '', ''),
('中區', '台中', '輕微*1', '', '', '', '', '', '輕微*2', '', '', '', '', ''),
('中區', '彰化', '', '', '', '', '', '', '', '輕微*1 嚴重*1', '', '', '', ''),
('南區', '台南', '', '', '', '', '', '', '', '', '', '嚴重*1', '', ''),
('南區', '高雄', '', '', '輕微*1', '', '', '輕微*1', '', '', '', '', '中等*1', '')

--#result 希望呈現的結果1
SELECT * FROM #result ORDER BY [area],[city]

DELETE FROM #result
INSERT INTO #result ([area],[city],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
VALUES
('北區', '台北', '嚴重', '', '', '', '輕微', '', '', '', '', '', '', ''),
('北區', '桃園', '', '', '', '中等', '嚴重', '', '', '', '', '', '', ''),
('北區', '新竹', '', '', '', '', '', '', '', '', '中等', '', '', ''),
('中區', '台中', '輕微', '', '', '', '', '', '輕微,輕微', '', '', '', '', ''),
('中區', '彰化', '', '', '', '', '', '', '', '輕微,嚴重', '', '', '', ''),
('南區', '台南', '', '', '', '', '', '', '', '', '', '嚴重', '', ''),
('南區', '高雄', '', '', '輕微', '', '', '輕微', '', '', '', '', '中等', '')

--#result 希望呈現的結果2
SELECT * FROM #result ORDER BY [area],[city]

請教大家
我有一個#table，紀錄每個月各地發生異常事件的嚴重度，
我使用PIVOT只能統計出每月的次數(如第2個select結果)

但想在每月下列出如【輕微x1 嚴重x1】(如第1個#result)
或【輕微,嚴重】(如第2個#result)這樣的呈現方式

請問該怎麼修改呢?

前提:
因為不會跨年度，所以月份固定是1~12月
但area與city日後可能會增減

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

純真的人

iT邦大師 1 級 ‧ 2021-12-09 12:35:22

最佳解答

參考 for xml path('')方式~

第一個結果

--#table 原始資料
declare @table table
(
	[month] int,
	[error] nvarchar(10),
	[area] nvarchar(10),
	[city] nvarchar(10)
)

INSERT INTO @table ([month],[error],[area],[city])
VALUES
('1', '嚴重', '北區', '台北'),
('1', '輕微', '中區', '台中'),
('3', '輕微', '南區', '高雄'),
('4', '中等', '北區', '桃園'),
('5', '輕微', '北區', '台北'),
('5', '嚴重', '北區', '桃園'),
('6', '輕微', '南區', '高雄'),
('7', '輕微', '中區', '台中'),
('7', '輕微', '中區', '台中'),
('8', '輕微', '中區', '彰化'),
('8', '嚴重', '中區', '彰化'),
('9', '中等', '北區', '新竹'),
('10', '嚴重', '南區', '台南'),
('11', '中等', '南區', '高雄')

--使用PIVOT
SELECT *
FROM 
    (
		select [area]
		,[city]
		,[month]
		,Stuff((
			select ' ' + error
			from (
				select b.[error] + '*' + Convert(varchar,COUNT([error])) error 
				from @table as b
				where a.[area] = b.[area]
				and a.[city] = b.[city]
				and a.[month] = b.[month]
				group by b.[area],b.[city],b.[month],b.[error]
			) k
			for xml path('')
		),1,1,'') [error]
		from @table a
		group by [area]
		,[city]
		,[month]
    ) AS p
PIVOT
(
    max([error]) FOR [month] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS pt
ORDER BY [area],[city]

第二個結果

--#table 原始資料
declare @table table
(
	[month] int,
	[error] nvarchar(10),
	[area] nvarchar(10),
	[city] nvarchar(10)
)

INSERT INTO @table ([month],[error],[area],[city])
VALUES
('1', '嚴重', '北區', '台北'),
('1', '輕微', '中區', '台中'),
('3', '輕微', '南區', '高雄'),
('4', '中等', '北區', '桃園'),
('5', '輕微', '北區', '台北'),
('5', '嚴重', '北區', '桃園'),
('6', '輕微', '南區', '高雄'),
('7', '輕微', '中區', '台中'),
('7', '輕微', '中區', '台中'),
('8', '輕微', '中區', '彰化'),
('8', '嚴重', '中區', '彰化'),
('9', '中等', '北區', '新竹'),
('10', '嚴重', '南區', '台南'),
('11', '中等', '南區', '高雄')

--使用PIVOT
SELECT *
FROM 
    (
		select [area]
		,[city]
		,[month]
		,Stuff((
			select ',' + b.[error]
			from @table as b
			where a.[area] = b.[area]
			and a.[city] = b.[city]
			and a.[month] = b.[month]
			for xml path('')
		),1,1,'') [error]
		from @table a
		group by [area]
		,[city]
		,[month]
    ) AS p
PIVOT
(
    max([error]) FOR [month] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS pt
ORDER BY [area],[city]