Paste this into a new question or an answer at dba.stackexchange.com:
<!-- --> > 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 > GO > > <pre> > 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 > </pre> <!-- --> > -- 版主原 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 > GO > > <pre> > empno | effecttime | 本薪 | 全勤獎 | 津貼1 | 津貼2 > :---- | :--------- | :----- | :-------- | :------ | :------ > 12345 | 2021-03-01 | 23800 | 100 | 0 | 410 > 12345 | 2021-09-01 | 24800 | <em>null</em> | <em>null</em> | 510 > 12345 | 2021-10-01 | <em>null</em> | 110 | 490 | <em>null</em> > 24680 | 2021-04-01 | 238009 | 1009 | 220 | 4109 > 24680 | 2021-05-01 | <em>null</em> | <em>null</em> | <em>null</em> | 5107 > 24680 | 2021-07-01 | 218009 | <em>null</em> | <em>null</em> | <em>null</em> > 24680 | 2021-08-01 | <em>null</em> | <em>null</em> | <em>null</em> | 5108 > 24680 | 2021-10-01 | 228009 | <em>null</em> | <em>null</em> | <em>null</em> > 24680 | 2021-11-01 | <em>null</em> | <em>null</em> | <em>null</em> | 5109 > 24680 | 2021-12-01 | <em>null</em> | 1109 | 4909 | <em>null</em> > </pre> <!-- --> > 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 > GO > > <pre> > 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 > </pre> *db<>fiddle [here](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=0bddae244407618bfda0442ed0d191a3)*
back to fiddle