行转列笔记

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