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
