质检单动态行转列合并
MSSQL2000 质检单动态行转列合并
pquality质检单据据头
billid ,billcode, billdate , traderid
1001, ,pq0002, 2013-09-01, 111111
pqualitydetail质检单单据体 (单据明细) 注mxitemno
billid,itemno, materialid,mxitemno,quantity
1001, 1 , A003 , 1 , 100
1001, 2 , A004 , 2 , 300
1001, 3 , K008 , 3 , 800
pqualitydetail2不合格原因明细 注mxitemno (注不合格原因有可能是动态变化的)
billid,itemno,mxitemno,unpassquantity,unpassremark
1001 , 1 , 3 , 30 , 杂质
1001 , 1 , 3 , 40 , 裂纹
1001 , 1 , 3 , 20 , 漏水
要求结果：
billid,billcode,billdate,traderid,materialid,quantity,unpassquantityandremark
1001 ,pq0002, 2013-09-01,111111 , K003, , 800 ,30杂质、40裂纹、20漏水
[解决办法]
--sql2000中只能用自定义的函数
IF OBJECT_ID('dbo.fn_merger', 'FN') IS NOT NULL
DROP FUNCTION dbo.fn_merger
GO
CREATE FUNCTION fn_merger(@billid VARCHAR(255), @mxitemno INT)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @rv NVARCHAR(MAX)
SET @rv = N''
SELECT @rv = @rv + N'、' + LTRIM(unpassquantity)+unpassremark FROM dbo.pqualitydetail2 WHERE billid = @billid AND mxitemno = @mxitemno
RETURN STUFF(@rv,1,1,'')
END
GO
--sql:
SELECT
a.billid,a.billcode,a.billdate,a.traderid,
b.materialid,
b.mxitemno,
b.quantity,
dbo.fn_merger(c.billid, c.mxitemno)
FROM pquality a
INNER JOIN pqualitydetail b
ON a.billid = b.billid
INNER JOIN pqualitydetail2 c
ON b.billid = c.billid
AND b.mxitemno = c.mxitemno
GROUP BY
a.billid,a.billcode,a.billdate,a.traderid,
b.materialid,
b.mxitemno,
b.quantity
