很显然, SELECT * FROM (select DEPT,PKG,LOT'+@SQL+',TOTAL=sum(NQTY) from T(NOLOCK) Group by DEPT,PKG,LOT with rollup ) G LEFT OUTER JOIN (SELECT LOT AS HLOT,LOTNAME,STOCK1,STOCK2,STOCK3,STOCK4,STOCK5 FROM T(NOLOCK) WHERE ) H ON G.LOT=H.HLOT返回的结果集中已经没有了 NQTY和CODE字段
TRY: ----------------------------------------------------------- DECLARE @SQL1 VARCHAR(8000) DECLARE @SQL2 VARCHAR(8000) DECLARE @ESQL VARCHAR(8000)SET @SQL1='' SELECT @SQL1= @SQL1+ ','+quotename(B.NAME,'''')+'' FROM T A(NOLOCK),Codename B(NOLOCK) WHERE A.CODE = B.CODE GROUP BY A.CODE,B.NAMESET @SQL2='' SELECT @SQL2= @SQL2+ ','+quotename(B.NAME,'''')+'=isnull(sum(CASE CODE when '+quotename(A.CODE,'''')+' THEN NQTY END),0)' FROM T A(NOLOCK),Codename B(NOLOCK) WHERE A.CODE = B.CODE GROUP BY A.CODE,B.NAMESet @ESQL = ' SELECT P.DEPT,P.PKG,P.LOT'+@SQL1+' FROM ( SELECT * FROM ( SELECT DEPT,PKG,LOT'+@SQL2+',TOTAL=sum(NQTY) from T(NOLOCK) Group by DEPT,PKG,LOT with rollup ) G LEFT OUTER JOIN (SELECT LOT AS HLOT,LOTNAME,STOCK1,STOCK2,STOCK3,STOCK4,STOCK5 FROM T(NOLOCK) WHERE ) H ON G.LOT=H.HLOT ) P ' Exec(@ESQL)
SET @SQL1='' SELECT @SQL1= @SQL1+ ','+quotename(B.NAME,'''')+'' 应该是 SET @SQL1='' SELECT @SQL1= @SQL1+ ','+quotename(B.NAME,'')+''
--改成这样:DECLARE @SQL VARCHAR(8000) DECLARE @ESQL VARCHAR(8000) DECLARE @S VARCHAR(8000)SELECT @SQL='',@s='' SELECT @SQL= @SQL+ ','+quotename(B.NAME,'''') +'=isnull(sum(CASE CODE when '+quotename(A.CODE,'''') +' THEN NQTY END),0)', @s=@s+',g.'+quotename(B.NAME,'''') FROM T A(NOLOCK),Codename B(NOLOCK) WHERE A.CODE = B.CODE GROUP BY A.CODE,B.NAME Set @ESQL = ' SELECT P.DEPT,P.PKG,P.LOT'+@s+' FROM ( SELECT * FROM ( select DEPT,PKG,LOT'+@SQL+',TOTAL=sum(NQTY) from T(NOLOCK) Group by DEPT,PKG,LOT with rollup ) G LEFT OUTER JOIN ( SELECT LOT AS HLOT,LOTNAME,STOCK1,STOCK2,STOCK3,STOCK4,STOCK5 FROM T(NOLOCK) WHERE ) H ON G.LOT=H.HLOT P ' Exec(@ESQL)
DECLARE @SQL VARCHAR(8000) DECLARE @ESQL VARCHAR(8000) DECLARE @S VARCHAR(8000)SELECT @SQL='',@s='' SELECT @SQL= @SQL+ ','+quotename(B.NAME,'''') +'=isnull(sum(CASE CODE when '+quotename(A.CODE,'''') +' THEN NQTY END),0)', @s=@s+',g.'+quotename(B.NAME,'''') FROM T A(NOLOCK),Codename B(NOLOCK) WHERE A.CODE = B.CODE GROUP BY A.CODE,B.NAME Set @ESQL = ' SELECT P.DEPT,P.PKG,P.LOT'+@s+' FROM ( SELECT * FROM ( select DEPT,PKG,LOT'+@SQL+',TOTAL=sum(NQTY) from T(NOLOCK) Group by DEPT,PKG,LOT with rollup ) G LEFT OUTER JOIN ( SELECT LOT AS HLOT,LOTNAME,STOCK1,STOCK2,STOCK3,STOCK4,STOCK5 FROM T(NOLOCK) WHERE 1=1 --*************这里的条件也不对 ) H ON G.LOT=H.HLOT P ' Exec(@ESQL)
SELECT * FROM
(select DEPT,PKG,LOT'+@SQL+',TOTAL=sum(NQTY) from T(NOLOCK)
Group by DEPT,PKG,LOT with rollup ) G
LEFT OUTER JOIN
(SELECT LOT AS HLOT,LOTNAME,STOCK1,STOCK2,STOCK3,STOCK4,STOCK5
FROM T(NOLOCK) WHERE ) H
ON
G.LOT=H.HLOT返回的结果集中已经没有了 NQTY和CODE字段
-----------------------------------------------------------
DECLARE @SQL1 VARCHAR(8000)
DECLARE @SQL2 VARCHAR(8000)
DECLARE @ESQL VARCHAR(8000)SET @SQL1=''
SELECT @SQL1= @SQL1+ ','+quotename(B.NAME,'''')+''
FROM T A(NOLOCK),Codename B(NOLOCK) WHERE A.CODE = B.CODE GROUP BY A.CODE,B.NAMESET @SQL2=''
SELECT @SQL2= @SQL2+ ','+quotename(B.NAME,'''')+'=isnull(sum(CASE CODE when '+quotename(A.CODE,'''')+' THEN NQTY END),0)'
FROM T A(NOLOCK),Codename B(NOLOCK) WHERE A.CODE = B.CODE GROUP BY A.CODE,B.NAMESet @ESQL = '
SELECT P.DEPT,P.PKG,P.LOT'+@SQL1+' FROM (
SELECT * FROM (
SELECT DEPT,PKG,LOT'+@SQL2+',TOTAL=sum(NQTY) from T(NOLOCK)
Group by DEPT,PKG,LOT with rollup ) G
LEFT OUTER JOIN
(SELECT LOT AS HLOT,LOTNAME,STOCK1,STOCK2,STOCK3,STOCK4,STOCK5 FROM T(NOLOCK) WHERE ) H
ON G.LOT=H.HLOT ) P '
Exec(@ESQL)
SELECT @SQL1= @SQL1+ ','+quotename(B.NAME,'''')+''
应该是
SET @SQL1=''
SELECT @SQL1= @SQL1+ ','+quotename(B.NAME,'')+''
DECLARE @ESQL VARCHAR(8000)
DECLARE @S VARCHAR(8000)SELECT @SQL='',@s=''
SELECT @SQL= @SQL+ ','+quotename(B.NAME,'''')
+'=isnull(sum(CASE CODE when '+quotename(A.CODE,'''')
+' THEN NQTY END),0)',
@s=@s+',g.'+quotename(B.NAME,'''')
FROM T A(NOLOCK),Codename B(NOLOCK)
WHERE A.CODE = B.CODE
GROUP BY A.CODE,B.NAME
Set @ESQL = '
SELECT P.DEPT,P.PKG,P.LOT'+@s+' FROM (
SELECT * FROM (
select DEPT,PKG,LOT'+@SQL+',TOTAL=sum(NQTY)
from T(NOLOCK)
Group by DEPT,PKG,LOT with rollup
) G
LEFT OUTER JOIN (
SELECT LOT AS HLOT,LOTNAME,STOCK1,STOCK2,STOCK3,STOCK4,STOCK5
FROM T(NOLOCK) WHERE
) H ON G.LOT=H.HLOT
P '
Exec(@ESQL)
DECLARE @ESQL VARCHAR(8000)
DECLARE @S VARCHAR(8000)SELECT @SQL='',@s=''
SELECT @SQL= @SQL+ ','+quotename(B.NAME,'''')
+'=isnull(sum(CASE CODE when '+quotename(A.CODE,'''')
+' THEN NQTY END),0)',
@s=@s+',g.'+quotename(B.NAME,'''')
FROM T A(NOLOCK),Codename B(NOLOCK)
WHERE A.CODE = B.CODE
GROUP BY A.CODE,B.NAME
Set @ESQL = '
SELECT P.DEPT,P.PKG,P.LOT'+@s+' FROM (
SELECT * FROM (
select DEPT,PKG,LOT'+@SQL+',TOTAL=sum(NQTY)
from T(NOLOCK)
Group by DEPT,PKG,LOT with rollup
) G
LEFT OUTER JOIN (
SELECT LOT AS HLOT,LOTNAME,STOCK1,STOCK2,STOCK3,STOCK4,STOCK5
FROM T(NOLOCK) WHERE 1=1 --*************这里的条件也不对
) H ON G.LOT=H.HLOT
P '
Exec(@ESQL)