求sql得写法，谢谢！

[eaqpi]

MID CODE DATE IN OUT
1 A-0001 2019-3-14 150
2 A-0002 2019-3-15 300
3 A-0003 2019-3-16 300
4 A-0004 2019-3-17 160 440


DID MID KIND Detail pay
1 1 收款 材料费A 50
2 1 收款 材料费A 100
3 2 付款 材料费B 100
4 2 付款 材料费C 200
5 3 付款 材料费B 80
6 3 付款 材料费B 90
7 3 付款 材料费D 130
8 4 收款 材料费A 160
9 4 付款 材料费C 400
10 4 付款 材料费B 20
11 4 付款 材料费B 20

求出来得结果为：
MID CODE DATE KIND收 收款内容 KIND付 付款内容 收入金额 支付金额
1 A-0001 2019-3-14 收款 材料费A 150
2 A-0002 2019-3-15 付款 材料费A，材料费B 300
3 A-0003 2019-3-16 付款 材料费B，材料费D 300
4 A-0004 2019-3-17 收款 材料费A 付款 材料费B，材料费C 160 440

[Hello World,]

DECLARE @t1 TABLE ([MID] int NOT NULL, CODE varchar(10) NOT NULL, [DATE] date NOT NULL, [IN] int NULL, [OUT] int NULL)
DECLARE @t2 TABLE(DID INT NOT NULL,	MID INT NOT NULL, KIND NVARCHAR(10) NOT NULL, Detail NVARCHAR(50) NULL,	pay INT NOT NULL)

INSERT @t1(MID, CODE, [DATE], [IN], [OUT])
VALUES(1,'A-0001','2019-03-14',150,NULL),
(2,'A-0002','2019-03-15',NULL,300),
(3,'A-0003','2019-03-16',NULL,300),
(4,'A-0004','2019-03-17',160,440)

INSERT @t2(DID, MID, KIND, Detail, pay)
VALUES (1,1,'收款','材料费A',50),
(2,1,'收款','材料费A',100),
(3,2,'付款','材料费B',100),
(4,2,'付款','材料费C',200),
(5,3,'付款','材料费B',80) ,
(6,3,'付款','材料费B',90) ,
(7,3,'付款','材料费D',130),
(8,4,'收款','材料费A',160),
(9,4,'付款','材料费C',400),
(10,4,'付款','材料费B',20),
(11,4,'付款','材料费B',20)


SELECT MID,	CODE,[DATE], CASE WHEN ISNULL([IN],0)>0 THEN '收款' ELSE '' END KIND收,
STUFF((SELECT DISTINCT ','+ Detail FROM @t2 WHERE MID=t1.mid AND KIND='收款' FOR XML PATH('')),1,1,'') 收款内容, 
CASE WHEN ISNULL([OUT],0)>0 THEN '付款' ELSE '' END KIND付,	
STUFF((SELECT DISTINCT ','+ Detail FROM @t2 WHERE MID=t1.mid AND KIND='付款' FOR XML PATH('')),1,1,'') 付款内容, 
[IN] 收入金额, [OUT] 支付金额 FROM @t1 t1

[leo_lesley]

create  table  a1 (MID int ,  CODE varchar(10), DATE varchar(10), [IN] int , OUT int) 
go
insert a1
select 1 ,'A-0001','2019-3-14', 150 , null
union select 2 ,'A-0002','2019-3-15', 300, null
union select 3 ,'A-0003','2019-3-16', 300, null
union select 4 ,'A-0004','2019-3-17', 160 ,440

go
create  table  a2 (DID int , MID int ,  KIND varchar(10), Detail varchar(10), pay int ) 
go
insert a2
 select 1 ,1 ,'收款','材料费A', 50
union select 2 ,1 ,'收款','材料费A', 100
union select 3 ,2 ,'付款','材料费B', 100
union select 4 ,2 ,'付款','材料费C', 200
union select 5 ,3 ,'付款','材料费B', 80
union select 6 ,3 ,'付款','材料费B', 90
union select 7 ,3 ,'付款','材料费D', 130
union select 8 ,4 ,'收款','材料费A', 160
union select 9 ,4 ,'付款','材料费C', 400
union select 10 ,4 ,'付款','材料费B', 20
union select 11 ,4 ,'付款','材料费B', 20





select a.MID,CODE,[DATE],KIND, col1,[IN],OUT 
from a1 a join (select MID , KIND , sum(pay) pay,(SELECT Detail+',' FROM a2 where MID = a.MID FOR XML PATH('')) col1 from a2 a group  by MID, KIND ) b  on a.MID = b.MID

drop  table a1 , a2