SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE [HisPay](
[empno] NVARCHAR(20) NULL,
[subjectname] NVARCHAR(20) NULL,
[effecttime] NVARCHAR(20) NULL,
[disabledtime] NVARCHAR(20) NULL,
[reason] NVARCHAR(20) NULL,
[value] NVARCHAR(20) NULL
);

INSERT INTO [HisPay] ([empno] ,[subjectname] ,[effecttime] ,[disabledtime] ,[reason] ,[value]) 
VALUES ('12345',N'津貼2','2021-03-01','2021-08-31',N'其他','410'),
('12345',N'本薪','2021-03-01','2021-08-31',N'其他','23800'),
('12345',N'全勤獎','2021-03-01','2021-09-30',N'其他','100'),
('12345',N'津貼1','2021-03-01','2021-09-30',N'其他','0'),
('12345',N'本薪','2021-09-01','',N'晉升','24800'),
('12345',N'津貼2','2021-09-01','',N'晉升','510'),
('12345',N'全勤獎','2021-10-01','',N'其他','110'),
('12345',N'津貼1','2021-10-01','',N'其他','490'),
--
('24680',N'津貼2','2021-04-01','2021-08-31',N'其他','4109'),
('24680',N'本薪','2021-04-01','2021-08-31',N'其他','238009'),
('24680',N'全勤獎','2021-04-01','2021-09-30',N'其他','1009'),
('24680',N'津貼1','2021-04-01','2021-09-30',N'其他','220'),
('24680',N'津貼2','2021-05-01','',N'晉升','5107'),
('24680',N'本薪','2021-07-01','',N'晉升','218009'),
('24680',N'津貼2','2021-08-01','',N'晉升','5108'),
('24680',N'本薪','2021-10-01','',N'晉升','228009'),
('24680',N'津貼2','2021-11-01','',N'晉升','5109'),
('24680',N'全勤獎','2021-12-01','',N'其他','1109'),
('24680',N'津貼1','2021-12-01','',N'其他','4909');

SELECT *
FROM HisPay


empno
subjectname
effecttime
disabledtime
reason
value


12345
津貼2
2021-03-01
2021-08-31
其他
410

12345
本薪
2021-03-01
2021-08-31
其他
23800

12345
全勤獎
2021-03-01
2021-09-30
其他
100

12345
津貼1
2021-03-01
2021-09-30
其他
0

12345
本薪
2021-09-01

晉升
24800

12345
津貼2
2021-09-01

晉升
510

12345
全勤獎
2021-10-01

其他
110

12345
津貼1
2021-10-01

其他
490

24680
津貼2
2021-04-01
2021-08-31
其他
4109

24680
本薪
2021-04-01
2021-08-31
其他
238009

24680
全勤獎
2021-04-01
2021-09-30
其他
1009

24680
津貼1
2021-04-01
2021-09-30
其他
220

24680
津貼2
2021-05-01

晉升
5107

24680
本薪
2021-07-01

晉升
218009

24680
津貼2
2021-08-01

晉升
5108

24680
本薪
2021-10-01

晉升
228009

24680
津貼2
2021-11-01

晉升
5109

24680
全勤獎
2021-12-01

其他
1109

24680
津貼1
2021-12-01

其他
4909


-- 版主原 SQL
SELECT empno, effecttime, 
ISNULL([本薪],NULL) 本薪, 
ISNULL([全勤獎],NULL) 全勤獎, 
ISNULL([津貼1],NULL) 津貼1 , 
ISNULL([津貼2],NULL) 津貼2
FROM (
select empno, subjectname, effecttime, reason, value from HisPay
) as sourecTable
PIVOT( MAX( sourecTable.value )
FOR sourecTable.subjectname in ([本薪], [全勤獎], [津貼1], [津貼2] )
) PB


empno
effecttime
本薪
全勤獎
津貼1
津貼2


12345
2021-03-01
23800
100
0
410

12345
2021-09-01
24800
null
null
510

12345
2021-10-01
null
110
490
null

24680
2021-04-01
238009
1009
220
4109

24680
2021-05-01
null
null
null
5107

24680
2021-07-01
218009
null
null
null

24680
2021-08-01
null
null
null
5108

24680
2021-10-01
228009
null
null
null

24680
2021-11-01
null
null
null
5109

24680
2021-12-01
null
1109
4909
null


WITH CTE_X1 AS (
SELECT empno, effecttime, 
ISNULL([本薪],NULL) 本薪, 
ISNULL([全勤獎],NULL) 全勤獎, 
ISNULL([津貼1],NULL) 津貼1 , 
ISNULL([津貼2],NULL) 津貼2
FROM (
select empno, subjectname, effecttime, reason, value from HisPay
) as sourecTable
PIVOT( MAX( sourecTable.value )
FOR sourecTable.subjectname in ([本薪], [全勤獎], [津貼1], [津貼2] )
) PB),
CTE_X2 AS (
SELECT empno,effecttime AS effecttimeSTART,本薪,
LEAD(effecttime, 1, FORMAT(DATEADD(D, 30000, effecttime),'yyyy-MM-dd'))
OVER (PARTITION BY empno ORDER BY effecttime) AS effecttimeEND
FROM CTE_X1
WHERE [本薪] IS NOT NULL),
CTE_X3 AS (
SELECT empno,effecttime AS effecttimeSTART,全勤獎,
LEAD(effecttime, 1, FORMAT(DATEADD(D, 30000, effecttime),'yyyy-MM-dd'))
OVER (PARTITION BY empno ORDER BY effecttime) AS effecttimeEND
FROM CTE_X1
WHERE [全勤獎] IS NOT NULL),
CTE_X4 AS (
SELECT empno,effecttime AS effecttimeSTART,津貼1,
LEAD(effecttime, 1, FORMAT(DATEADD(D, 30000, effecttime),'yyyy-MM-dd'))
OVER (PARTITION BY empno ORDER BY effecttime) AS effecttimeEND
FROM CTE_X1
WHERE [津貼1] IS NOT NULL),
CTE_X5 AS (
SELECT empno,effecttime AS effecttimeSTART,津貼2,
LEAD(effecttime, 1, FORMAT(DATEADD(D, 30000, effecttime),'yyyy-MM-dd'))
OVER (PARTITION BY empno ORDER BY effecttime) AS effecttimeEND
FROM CTE_X1
WHERE [津貼2] IS NOT NULL)

SELECT A.empno,A.effecttime,
ISNULL(A.本薪,B.本薪) AS 本薪,
ISNULL(A.全勤獎,C.全勤獎) AS 全勤獎,
ISNULL(A.津貼1,D.津貼1) AS 津貼1,
ISNULL(A.津貼2,E.津貼2) AS 津貼2
FROM CTE_X1 AS A
LEFT JOIN CTE_X2 AS B ON A.empno=B.empno AND A.effecttime>=B.effecttimeSTART
AND A.effecttime<B.effecttimeEND
LEFT JOIN CTE_X3 AS C ON A.empno=C.empno AND A.effecttime>=C.effecttimeSTART
AND A.effecttime<C.effecttimeEND
LEFT JOIN CTE_X4 AS D ON A.empno=D.empno AND A.effecttime>=D.effecttimeSTART
AND A.effecttime<D.effecttimeEND
LEFT JOIN CTE_X5 AS E ON A.empno=E.empno AND A.effecttime>=E.effecttimeSTART
AND A.effecttime<E.effecttimeEND


empno
effecttime
本薪
全勤獎
津貼1
津貼2


12345
2021-03-01
23800
100
0
410

12345
2021-09-01
24800
100
0
510

12345
2021-10-01
24800
110
490
510

24680
2021-04-01
238009
1009
220
4109

24680
2021-05-01
238009
1009
220
5107

24680
2021-07-01
218009
1009
220
5107

24680
2021-08-01
218009
1009
220
5108

24680
2021-10-01
228009
1009
220
5108

24680
2021-11-01
228009
1009
220
5109

24680
2021-12-01
228009
1109
4909
5109

empno	subjectname	effecttime	disabledtime	reason	value
12345	津貼2	2021-03-01	2021-08-31	其他	410
12345	本薪	2021-03-01	2021-08-31	其他	23800
12345	全勤獎	2021-03-01	2021-09-30	其他	100
12345	津貼1	2021-03-01	2021-09-30	其他	0
12345	本薪	2021-09-01		晉升	24800
12345	津貼2	2021-09-01		晉升	510
12345	全勤獎	2021-10-01		其他	110
12345	津貼1	2021-10-01		其他	490
24680	津貼2	2021-04-01	2021-08-31	其他	4109
24680	本薪	2021-04-01	2021-08-31	其他	238009
24680	全勤獎	2021-04-01	2021-09-30	其他	1009
24680	津貼1	2021-04-01	2021-09-30	其他	220
24680	津貼2	2021-05-01		晉升	5107
24680	本薪	2021-07-01		晉升	218009
24680	津貼2	2021-08-01		晉升	5108
24680	本薪	2021-10-01		晉升	228009
24680	津貼2	2021-11-01		晉升	5109
24680	全勤獎	2021-12-01		其他	1109
24680	津貼1	2021-12-01		其他	4909