交叉表的应用较为丰富,应用的方式与范围也是十分丰富。
这里应以一个制造业中的工序流程卡的生成为例
原始数据:
最终的效果
静态生成角本
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)