限制列数的交叉表

限制列数的交叉数据报表

--示例数据:
CREATETABLEtest(
factoryidvarchar(20),
bagidint,
rollint,
numbernumeric(9,1),
UNIQUE(bagid,roll))
INSERTtestSELECT'M-CS-11#6/GREEN',1,1, 86
UNIONALLSELECT'M-CS-11#6/GREEN',1,2, 59.5
UNIONALLSELECT'M-CS-11#6/GREEN',1,3, 31.2
UNIONALLSELECT'M-CS-11#6/GREEN',1,4, 42
UNIONALLSELECT'M-CS-11#6/GREEN',1,5, 31
UNIONALLSELECT'M-CS-11#6/GREEN',1,6, 114.3

UNIONALLSELECT'M-CS-11#6/GREEN',2,7, 101
UNIONALLSELECT'M-CS-11#6/GREEN',2,8, 83.9
UNIONALLSELECT'M-CS-11#6/GREEN',2,9, 97.5
UNIONALLSELECT'M-CS-11#6/GREEN',2,10,105.4

UNIONALLSELECT'M-CS-11#6/GREEN',3,11,103
UNIONALLSELECT'M-CS-11#6/GREEN',3,12,128.5
UNIONALLSELECT'M-CS-11#6/GREEN',3,13,74.7
UNIONALLSELECT'M-CS-11#6/GREEN',3,14,107

UNIONALLSELECT'M-CS-11#6/GREEN',4,15,73.4
UNIONALLSELECT'M-CS-11#6/GREEN',4,16,100
UNIONALLSELECT'M-CS-11#6/GREEN',4,17,141.5
GO

问题描述:
bagid,roll值唯一，需要将列roll水平显示，并且每条记录只显示4列，多余的自动换行。对于示例数据，要求结果如下（rolls是记录数）：
factoryidbagidrollsn1n2 n3 n4 Total
-----------------------------------------------------------------------------------------------
M-CS-11#6/GREEN1 6 86.059.5 31.2 42.0
M-CS-11#6/GREEN1 31.0114.3 364.0
M-CS-11#6/GREEN2 4 101.083.9 97.5 105.4 387.8
M-CS-11#6/GREEN3 4 103.0128.574.7 107.0 413.2
M-CS-11#6/GREEN4 3 73.4 100.0141.5 314.9
Total 1479.9

（所影响的行数为6行）

--查询处理代码
SELECTa.factoryid,a.bagid,
rolls=CASE
WHENa.roll=0THENCAST(b.rollsasvarchar)
ELSE''END,
a.n1,a.n2,a.n3,a.n4,
Total=CASE
WHENa.rollISNULLTHENCAST(a.Totalasvarchar)
WHENa.roll=(b.rolls-1)/4THENCAST(b.Totalasvarchar)
ELSE''END
FROM(
SELECTfactoryid=CASE
WHENGROUPING(factoryid)=1THEN'Total'
ELSEfactoryidEND,
bagid=CASE
WHENGROUPING(factoryid)=1THEN''
ELSECAST(bagidASVARCHAR)END,
n1=CASE
WHENGROUPING(factoryid)=1THEN''
ELSECAST(SUM(CASEroll%4WHEN0THENnumberEND)ASVARCHAR)END,
n2=CASE
WHENGROUPING(factoryid)=1THEN''
ELSEISNULL(CAST(SUM(CASEroll%4WHEN1THENnumberEND)ASVARCHAR),'')END,
n3=CASE
WHENGROUPING(factoryid)=1THEN''
ELSEISNULL(CAST(SUM(CASEroll%4WHEN2THENnumberEND)ASVARCHAR),'')END,
n4=CASE
WHENGROUPING(factoryid)=1THEN''
ELSEISNULL(CAST(SUM(CASEroll%4WHEN3THENnumberEND)ASVARCHAR),'')END,
Total=SUM(number),
roll=roll/4
FROM(
SELECTfactoryid,bagid,number,
roll=(SELECTCOUNT(DISTINCTroll)
FROMtest
WHEREfactoryid=a.factoryid
ANDbagid=a.bagid
ANDroll<a.roll)
FROMtesta
)aGROUPBYfactoryid,bagid,roll/4WITHROLLUP
HAVINGGROUPING(factoryid)=1ORGROUPING(roll/4)=0
)a
LEFTJOIN(
SELECTfactoryid,bagid,
rolls=COUNT(*),
Total=SUM(number)
FROMtest
GROUPBYfactoryid,bagid
)bONa.factoryid=b.factoryid
ANDa.bagid=b.bagid
GO

原帖地址