SQL动态生成交叉表应用范例

交叉表的应用较为丰富，应用的方式与范围也是十分丰富。

这里应以一个制造业中的工序流程卡的生成为例

原始数据：

最终的效果

静态生成角本

SELECT 
T.P,T.M,
T1.WORKNAME AS W1,T2.WORKNAME AS W2,T3.WORKNAME AS W3,
T4.WORKNAME AS W4,T5.WORKNAME AS W5,T6.WORKNAME AS W6, 1 AS S 
FROM 
(SELECT distinct P,M FROM RR GROUP BY P,M)T 
LEFT JOIN(SELECT P,M,WORKNAME FROM RR WHERE K=1) T1 ON T.P=T1.P AND T.M=T1.M  
LEFT JOIN(SELECT P,M,WORKNAME FROM RR WHERE K=2) T2 ON T.P=T2.P AND T.M=T2.M  
LEFT JOIN(SELECT P,M,WORKNAME FROM RR WHERE K=3) T3 ON T.P=T3.P AND T.M=T3.M  
LEFT JOIN(SELECT P,M,WORKNAME FROM RR WHERE K=4) T4 ON T.P=T4.P AND T.M=T4.M  
LEFT JOIN(SELECT P,M,WORKNAME FROM RR WHERE K=5) T5 ON T.P=T5.P AND T.M=T5.M 
LEFT JOIN(SELECT P,M,WORKNAME FROM RR WHERE K=6) T6 ON T.P=T6.P AND T.M=T6.M 

动态生成角本

-----------------------------------------------------------
IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE name='RR')
BEGIN
	DROP TABLE RR
END
-----------------------------------------------------------
select p,m,ROW_NUMBER()OVER (ORDER BY P,M) AS K,workname 
INTO RR
from r 
WHERE 1=2
-----------------------------------------------------------
declare @p varchar(3),@m varchar(4)
declare Rx cursor for select P,m from r GROUP BY P,M
open rx
fetch next from rx into @p,@m
WHILE @@FETCH_STATUS =0
BEGIN
INSERT INTO RR
select p,m,ROW_NUMBER()OVER (ORDER BY P,M) AS K,workname from r 
WHERE P=@P AND M=@M
fetch next from rx into @p,@m
END 
CLOSE RX
DEALLOCATE RX
GO
-----------------------------------------------------------
DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT T.P,T.M,'
SELECT @SQL=@SQL+'T'+K+'.WORKNAME AS R'+K+',' 
FROM (SELECT DISTINCT RTRIM(CAST(K AS CHAR)) AS K FROM RR)A
SELECT @SQL=@SQL+' 1 AS S FROM (SELECT P,M FROM RR GROUP BY P,M)T'
SELECT @SQL=@SQL+' LEFT JOIN(SELECT P,M,WORKNAME FROM RR WHERE K='+K+') T'+
K+' ON T.P=T'+K+'.P AND T.M=T'+K+'.M '
FROM (SELECT DISTINCT RTRIM(CAST(K AS CHAR)) AS K FROM RR)A
EXEC (@SQL)