iT邦幫忙

1

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

--#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 }} |
{{ formatDate(item.duration) }}
直播中

2 個回答

3
純真的人
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]

https://ithelp.ithome.com.tw/upload/images/20211209/200613698BtzVeYbvj.png

第二個結果

--#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]

https://ithelp.ithome.com.tw/upload/images/20211209/20061369plVBCvQJXE.png

wu2960 iT邦新手 3 級 ‧ 2021-12-09 14:14:18 檢舉

我後來用迴圈去解,但太過冗長,還是您的簡潔易懂,非常感謝您的幫忙!

2
pilipala
iT邦新手 2 級 ‧ 2021-12-09 21:58:34

SQL Server 2017 有 STRING_AGG() 可以使用

我要發表回答

立即登入回答