DECLARE @sql VARCHAR (MAX),@sql2 VARCHAR (MAX),@sql3 VARCHAR (MAX)
--设置行转列字段
SELECT @sql = ''
SELECT @sql = @sql + ',' + '[' + rtrim (b.bugno) + ']' FROM (SELECT DISTINCT q.bugno,qd.bugname FROM qaaETmxb q right JOIN dbo.QAAqdmxb qd ON q.bugno=qd.bugno WHERE q.bugno<>'') b
SELECT @sql=STUFF(@sql, 1 , 1, '')
--求和 行转列的字段
SELECT @sql3 = ''
SELECT @sql3 = @sql3 + ',' + 'SUM([' + rtrim (b.bugno) + ']) [' + rtrim (b.bugname) + ']' FROM (SELECT DISTINCT q.bugno,qd.bugname FROM qaaETmxb q right JOIN dbo.QAAqdmxb qd ON q.bugno=qd.bugno WHERE q.bugno<>'') b
SELECT @sql3 = STUFF(@sql3, 1 , 1, '')
SELECT @sql2 = 'SELECT chkno,pdctno,okqty,chkdate,checker,chkkind,keyin,indate,aggregation,yield,features,chkqty,'+@sql3+' FROM (SELECT q.chkno,pdctno,chkqty,okqty,chkdate,checker,chkkind,q.keyin,q.indate,aggregation,yield,features,bugqty,bugno FROM qaaETjcb q LEFT JOIN dbo.qaaETmxb qa ON q.chkno=qa.chkno) a PIVOT
(
SUM(bugqty)
FOR bugno IN ('+@sql+')
) r group by chkno,pdctno,chkqty,okqty,chkdate,checker,chkkind,keyin,indate,aggregation,yield,features'
exec (@sql2)
SELECT @sql2