源代码:
DECLARE @dt1 AS VARCHAR(10)
DECLARE @dt2 AS VARCHAR(10)
SET @dt1 = '2011-01-01'
SET @dt2 = '2011-01-05'
DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = ISNULL(@SQL + ',' , '') + '[' + CONVERT(VARCHAR(20),dtCreate,111) + ']'
FROM
(SELECT DISTINCT CONVERT(VARCHAR(20),dtCreate,111) dtCreate
FROM Sale_Order
WHERE
dtCreate >= @dt1
AND dtCreate <= @dt2
AND intObjectID >= 0
AND intObjectID <> 427
AND intObjectID <> 74
AND intObjectID <> 75
GROUP BY
dtCreate ) AS a
PRINT (@sql)DECLARE @S VARCHAR(MAX)
SET @S='SELECT
--a.strCode AS [商品编号]
--, a.strProduct AS [商品名]
--, a.strBrand AS [产品品牌]
--, d.strSupplier AS [供应商]
, '+ @SQL +'
FROM
Setting_Product a
INNER JOIN Sale_OrderDetails b ON a.intProductID=b.intProductID
INNER JOIN Sale_Order c ON b.intOrderID=c.intOrderID
INNER JOIN Setting_Supplier d ON d.intSupplierID=a.intSupplierID
GROUP BY
a.strCode
, a.strProduct
, a.strBrand
, d.strSupplier
PIVOT (SUM(c.intNumber) FOR c.dtCreate IN (' + @SQL + ')) b'
--EXEC (@S)
PRINT(@S)运行结果如下:2011/1/1 2011/1/2 2011/1/3
1 NULL 1
177 175 125
232 272 191
53 26 69
1 1 7
23 16 21
NULL NULL 5
4 NULL 3
106 73 145
155 109 211
NULL NULL 6
108 123 240问怎么去除NULL值
希望结果:2011/1/1 2011/1/2 2011/1/3
1 0 1
177 175 125
232 272 191
53 26 69
1 1 7
23 16 21
0 0 5
4 0 3
106 73 145
155 109 211
0 0 6
108 123 240
DECLARE @dt1 AS VARCHAR(10)
DECLARE @dt2 AS VARCHAR(10)
SET @dt1 = '2011-01-01'
SET @dt2 = '2011-01-05'
DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = ISNULL(@SQL + ',' , '') + '[' + CONVERT(VARCHAR(20),dtCreate,111) + ']'
FROM
(SELECT DISTINCT CONVERT(VARCHAR(20),dtCreate,111) dtCreate
FROM Sale_Order
WHERE
dtCreate >= @dt1
AND dtCreate <= @dt2
AND intObjectID >= 0
AND intObjectID <> 427
AND intObjectID <> 74
AND intObjectID <> 75
GROUP BY
dtCreate ) AS a
PRINT (@sql)DECLARE @S VARCHAR(MAX)
SET @S='SELECT
--a.strCode AS [商品编号]
--, a.strProduct AS [商品名]
--, a.strBrand AS [产品品牌]
--, d.strSupplier AS [供应商]
, '+ @SQL +'
FROM
Setting_Product a
INNER JOIN Sale_OrderDetails b ON a.intProductID=b.intProductID
INNER JOIN Sale_Order c ON b.intOrderID=c.intOrderID
INNER JOIN Setting_Supplier d ON d.intSupplierID=a.intSupplierID
GROUP BY
a.strCode
, a.strProduct
, a.strBrand
, d.strSupplier
PIVOT (SUM(c.intNumber) FOR c.dtCreate IN (' + @SQL + ')) b'
--EXEC (@S)
PRINT(@S)运行结果如下:2011/1/1 2011/1/2 2011/1/3
1 NULL 1
177 175 125
232 272 191
53 26 69
1 1 7
23 16 21
NULL NULL 5
4 NULL 3
106 73 145
155 109 211
NULL NULL 6
108 123 240问怎么去除NULL值
希望结果:2011/1/1 2011/1/2 2011/1/3
1 0 1
177 175 125
232 272 191
53 26 69
1 1 7
23 16 21
0 0 5
4 0 3
106 73 145
155 109 211
0 0 6
108 123 240
PIVOT (SUM(isnull(c.intNumber),0) FOR c.dtCreate IN (' + @SQL + ')) b'
DECLARE @dt1 AS VARCHAR(10)
DECLARE @dt2 AS VARCHAR(10)
SET @dt1 = '2011-01-01'
SET @dt2 = '2011-01-05'
DECLARE @SQL VARCHAR(MAX),@SQL2 VARCHAR(MAX)SELECT @SQL = ISNULL(@SQL + ',' , '') + '[' + CONVERT(VARCHAR(20),dtCreate,111) + ']'
FROM
(SELECT DISTINCT CONVERT(VARCHAR(20),dtCreate,111) dtCreate
FROM Sale_Order
WHERE
dtCreate >= @dt1
AND dtCreate <= @dt2
AND intObjectID >= 0
AND intObjectID <> 427
AND intObjectID <> 74
AND intObjectID <> 75
GROUP BY dtCreate) AS aselect @SQL2=ISNULL(@SQL + ',' , '')+'isnull(['
+ CONVERT(VARCHAR(20),dtCreate,111) + '],0) '
+ '['+ CONVERT(VARCHAR(20),dtCreate,111)+']'
FROM
(SELECT DISTINCT CONVERT(VARCHAR(20),dtCreate,111) dtCreate
FROM Sale_Order
WHERE
dtCreate >= @dt1
AND dtCreate <= @dt2
AND intObjectID >= 0
AND intObjectID <> 427
AND intObjectID <> 74
AND intObjectID <> 75
GROUP BY dtCreate) AS aDECLARE @S VARCHAR(MAX)
SET @S='SELECT
--a.strCode AS [商品编号]
--, a.strProduct AS [商品名]
--, a.strBrand AS [产品品牌]
--, d.strSupplier AS [供应商]
, '+ @SQL2 +'
FROM
Setting_Product a
INNER JOIN Sale_OrderDetails b ON a.intProductID=b.intProductID
INNER JOIN Sale_Order c ON b.intOrderID=c.intOrderID
INNER JOIN Setting_Supplier d ON d.intSupplierID=a.intSupplierID
GROUP BY
a.strCode
, a.strProduct
, a.strBrand
, d.strSupplier
PIVOT (SUM(c.intNumber) FOR c.dtCreate IN (' + @SQL + ')) b'--EXEC (@S)PRINT(@S)
出现ISNULL()为不可识别的聚合函数
DECLARE @dt1 AS VARCHAR(10)
DECLARE @dt2 AS VARCHAR(10)
SET @dt1 = '2011-01-01'
SET @dt2 = '2011-01-05'
DECLARE @SQL VARCHAR(MAX)SELECT @SQL = ISNULL(@SQL + ',' , '')
+'max(case when CONVERT(VARCHAR(20),c.dtCreate,111)='''+CONVERT(VARCHAR(20),dtCreate,111)
+''' then c.intNumber else 0 end) as '+'[' + CONVERT(VARCHAR(20),dtCreate,111) + ']'
FROM
(SELECT DISTINCT CONVERT(VARCHAR(20),dtCreate,111) dtCreate
FROM Sale_Order
WHERE
dtCreate >= @dt1
AND dtCreate <= @dt2
AND intObjectID >= 0
AND intObjectID <> 427
AND intObjectID <> 74
AND intObjectID <> 75
GROUP BY dtCreate) AS a
PRINT (@sql)DECLARE @S VARCHAR(MAX)
SET @S='SELECT
--a.strCode AS [商品编号]
--, a.strProduct AS [商品名]
--, a.strBrand AS [产品品牌]
--, d.strSupplier AS [供应商]
, '+ @SQL +'
FROM
Setting_Product a
INNER JOIN Sale_OrderDetails b ON a.intProductID=b.intProductID
INNER JOIN Sale_Order c ON b.intOrderID=c.intOrderID
INNER JOIN Setting_Supplier d ON d.intSupplierID=a.intSupplierID
GROUP BY
a.strCode
, a.strProduct
, a.strBrand
, d.strSupplier'
--EXEC (@S)
PRINT(@S)
如果用PIRVOT来写呢,怎么写?谢谢呀,麻烦啦
DECLARE @dt1 AS VARCHAR(10)
DECLARE @dt2 AS VARCHAR(10)
SET @dt1 = '2011-01-01'
SET @dt2 = '2011-01-05'
DECLARE @SQL VARCHAR(MAX),@SQL2 VARCHAR(MAX)SELECT @SQL = ISNULL(@SQL + ',' , '') + '[' + CONVERT(VARCHAR(20),dtCreate,111) + ']'
FROM
(SELECT DISTINCT CONVERT(VARCHAR(20),dtCreate,111) dtCreate
FROM Sale_Order
WHERE
dtCreate >= @dt1
AND dtCreate <= @dt2
AND intObjectID >= 0
AND intObjectID <> 427
AND intObjectID <> 74
AND intObjectID <> 75
GROUP BY dtCreate) AS aselect @SQL2=ISNULL(@SQL + ',' , '')+'isnull(['
+ CONVERT(VARCHAR(20),dtCreate,111) + '],0) '
+ '['+ CONVERT(VARCHAR(20),dtCreate,111)+']'
FROM
(SELECT DISTINCT CONVERT(VARCHAR(20),dtCreate,111) dtCreate
FROM Sale_Order
WHERE
dtCreate >= @dt1
AND dtCreate <= @dt2
AND intObjectID >= 0
AND intObjectID <> 427
AND intObjectID <> 74
AND intObjectID <> 75
GROUP BY dtCreate) AS aDECLARE @S VARCHAR(MAX)
SET @S='SELECT
--a.strCode AS [商品编号]
--, a.strProduct AS [商品名]
--, a.strBrand AS [产品品牌]
--, d.strSupplier AS [供应商]
, '+ @SQL2 +'
FROM
Setting_Product a
INNER JOIN Sale_OrderDetails b ON a.intProductID=b.intProductID
INNER JOIN Sale_Order c ON b.intOrderID=c.intOrderID
INNER JOIN Setting_Supplier d ON d.intSupplierID=a.intSupplierID
GROUP BY
a.strCode
, a.strProduct
, a.strBrand
, d.strSupplier
PIVOT (SUM(c.intNumber) FOR c.dtCreate IN (' + @SQL + ')) b'--EXEC (@S)PRINT(@S)