SQL Server 2017

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

--各工程總工資表
CREATE TABLE PrjAmount (
ID int NULL ,     -- 工程單號
Amount int NULL); -- 工程總工資

INSERT INTO PrjAmount (ID,Amount)
VALUES 
(1,900),
(2,900),
(3,900),
(4,900),
(5,900);

SELECT *
FROM PrjAmount;


ID
Amount


1
900

2
900

3
900

4
900

5
900


--各工程參與人員表
CREATE TABLE Attendance (
ID int NULL ,            -- 工程單號
Eng nvarchar(20) NULL);  -- 參與員工

INSERT INTO Attendance (ID,Eng)
VALUES 
(1,'Tech1'),
(1,'Tech2'),
(1,'Tech3'),
(1,'Tech4'),
(2,'Tech1'),
(2,'Tech2'),
(2,'Tech3'),
(3,'Tech1'),
(3,'Tech2'),
(4,'Tech1');

SELECT *
FROM Attendance;


ID
Eng


1
Tech1

1
Tech2

1
Tech3

1
Tech4

2
Tech1

2
Tech2

2
Tech3

3
Tech1

3
Tech2

4
Tech1


SELECT ID,Amount,ManCounter AS '參與人數',
MIN(CASE WHEN Eng='Tech1' THEN CAST(AmountAvg AS VARCHAR(20)) ELSE N'無同行' END) AS 'Tech1',
MIN(CASE WHEN Eng='Tech2' THEN CAST(AmountAvg AS VARCHAR(20)) ELSE N'無同行' END) AS 'Tech2',
MIN(CASE WHEN Eng='Tech3' THEN CAST(AmountAvg AS VARCHAR(20)) ELSE N'無同行' END) AS 'Tech3',
MIN(CASE WHEN Eng='Tech4' THEN CAST(AmountAvg AS VARCHAR(20)) ELSE N'無同行' END) AS 'Tech4'
FROM (
SELECT A.ID,A.Amount,B.Eng,
CASE WHEN Eng IS NULL THEN 0 ELSE
COUNT(1) OVER(PARTITION BY A.ID) END AS ManCounter,
A.Amount/COUNT(1) OVER(PARTITION BY A.ID) AS AmountAvg
FROM PrjAmount AS A
LEFT JOIN Attendance AS B ON B.ID=A.ID
) AS TempA
GROUP BY ID,Amount,ManCounter
ORDER BY ID,Amount


ID
Amount
參與人數
Tech1
Tech2
Tech3
Tech4


1
900
4
225
225
225
225

2
900
3
300
300
300
無同行

3
900
2
450
450
無同行
無同行

4
900
1
900
無同行
無同行
無同行

5
900
0
無同行
無同行
無同行
無同行

ID	Eng
1	Tech1
1	Tech2
1	Tech3
1	Tech4
2	Tech1
2	Tech2
2	Tech3
3	Tech1
3	Tech2
4	Tech1

ID	Amount	參與人數	Tech1	Tech2	Tech3	Tech4
1	900	4	225	225	225	225
2	900	3	300	300	300	無同行
3	900	2	450	450	無同行	無同行
4	900	1	900	無同行	無同行	無同行
5	900	0	無同行	無同行	無同行	無同行