请教SQL写数据透视表，用于数据分析

Question

问题遇到的现象和发生背景

希望能用SQL写一个数据透视图分析数据

我想要达到的结果，如果你需要快速回答，请尝试 “付费悬赏”

Answer 1

MSSQL的行转列

CREATE TABLE TMP(EA_msgtime DATETIME,EA_Re_Name VARCHAR(20))
insert into tmp  (EA_msgtime,EA_Re_Name) VALUES ('2023-1-11 12:47:20.000','测试点A') 
insert into tmp  (EA_msgtime,EA_Re_Name) VALUES ('2023-1-11 11:47:32.000','测试点B')
insert into tmp  (EA_msgtime,EA_Re_Name) VALUES ('2023-1-11 11:47:29.000','测试点A')
insert into tmp  (EA_msgtime,EA_Re_Name) VALUES ('2023-1-11 11:35:19.000','测试点B')
insert into tmp  (EA_msgtime,EA_Re_Name) VALUES ('2023-1-9 15:19:48.000','测试点A')
insert into tmp  (EA_msgtime,EA_Re_Name) VALUES ('2023-1-9 15:18:49.000','测试点B') 
insert into tmp  (EA_msgtime,EA_Re_Name) VALUES ('2023-1-9 15:18:14.000','测试点A')
insert into tmp  (EA_msgtime,EA_Re_Name) VALUES ('2022-11-24 12:47:20.000','测试点C')

--如果测试点是死的，可以如下
SELECT CONVERT(VARCHAR(10),EA_msgtime,120) AS EA_msgtime,
       SUM(CASE WHEN EA_Re_Name='测试点A' THEN 1 ELSE 0 END) 测试点A,
       SUM(CASE WHEN EA_Re_Name='测试点B' THEN 1 ELSE 0 END) 测试点B,
       SUM(CASE WHEN EA_Re_Name='测试点C' THEN 1 ELSE 0 END) 测试点C,
       COUNT(1) '总计'
FROM tmp
GROUP BY CONVERT(VARCHAR(10),EA_msgtime,120)

--如果测试点不固定，则用动态sql

DECLARE @sql VARCHAR(8000)
DECLARE @field VARCHAR(1000)

SET @field= ''
SELECT @field = @field +','+ 'SUM(CASE WHEN EA_Re_Name='''+EA_Re_Name+''' THEN 1 ELSE 0 END) AS '''+EA_Re_Name+'''' FROM tmp GROUP BY EA_Re_Name 

SELECT @sql = 'SELECT CONVERT(VARCHAR(10),EA_msgtime,120) AS EA_msgtime '+@field +',count(1) ''总计'' FROM tmp GROUP BY CONVERT(VARCHAR(10),EA_msgtime,120)'
EXEC(@SQL)
--result
EA_msgtime    测试点A    测试点B    测试点C    总计
2022-11-24    0    0    1    1
2023-01-09    2    1    0    3
2023-01-11    2    2    0    4