DataGrid绑定动态变化的列数-存储过程实现方式
1
--*******************************
2
--|功 能:送修退换机统计表
3
--|说 明:
4
--|作 者:dzh
5
--|创建时间:2005-12-13 10:45:42
6
--*******************************
7![](/Images/OutliningIndicators/None.gif)
8![](/Images/OutliningIndicators/None.gif)
9
CREATE PROCEDURE GetConvertStat_SP
10
@Start_Date varchar(20)='',
11
@End_Date varchar(20)='',
12
@ProductId varchar(4)='',
13
@UnitsSysId varchar(20)=''
14
AS
15
Declare @strWhr varchar(200) --查询条件[主查询]
16
select @strWhr=''
17![](/Images/OutliningIndicators/None.gif)
18
if (rtrim(@Start_Date)<>'')
19
begin
20
SELECT @strWhr=@strWhr+' AND Rep_GetDate>='''+rtrim(@Start_Date)+' 00:00:00'''
21
end
22
if (rtrim(@End_Date)<>'')
23
begin
24
SELECT @strWhr=@strWhr+' AND Rep_GetDate<='''+rtrim(@End_Date)+' 23:59:59'''
25
end
26
if (rtrim(@UnitsSysId)<>'')
27
begin
28
SELECT @strWhr=@strWhr+' AND Units_SysId='''+@UnitsSysId+''''
29
end
30![](/Images/OutliningIndicators/None.gif)
31![](/Images/OutliningIndicators/None.gif)
32
Declare @str varchar(5000)
33
select @str=''
34![](/Images/OutliningIndicators/None.gif)
35
If (rtrim(@ProductId)<>'')
36
Begin
37
SELECT
38
@str=@str + ',['+Product_Name+'<br>数量] = SUM(CASE Product_ID WHEN '''+ Product_ID +''' THEN Isnull(Qty, 0) ELSE 0 END)'
39
+ ',['+Product_Name+'<br>比例] = SUM(CASE Product_ID WHEN '''+ Product_ID +''' THEN Cast(Isnull(Qty, 0) as Decimal) ELSE 0 END)/SUM(Isnull(Qty, 0))'
40
FROM Rep_Info
41
WHERE Product_ID=@ProductId
42
GROUP BY Product_Name, Product_ID
43
End
44
Else
45
Begin
46
SELECT
47
@str=@str + ',['+Product_Name+'<br>数量] = SUM(CASE Product_ID WHEN '''+ Product_ID +''' THEN Isnull(Qty, 0) ELSE 0 END)'
48
+ ',['+Product_Name+'<br>比例] = SUM(CASE Product_ID WHEN '''+ Product_ID +''' THEN Cast(Isnull(Qty, 0) as Decimal) ELSE 0 END)/SUM(Isnull(Qty, 0))'
49
FROM Rep_Info
50
WHERE Product_Name IS NOT NULL AND Product_Name<>'' AND Product_ID IS NOT NULL AND Product_ID<>''
51
GROUP BY Product_Name, Product_ID
52
ORDER BY Product_Name, Product_ID
53
End
54
--print(@str)
55![](/Images/OutliningIndicators/None.gif)
56
EXEC('
57
SELECT Org_SimpleName' + @str + ',退换机总数=SUM(Isnull(Qty, 0))
58
FROM
59
(
60
SELECT TOP 100 PERCENT Org_SimpleName, Product_Name, Product_ID, Qty=COUNT(*) FROM Rep_Info_V
61
WHERE IsGet=''1'' AND Default_DisposeID IN (''5'',''6'',''7'') --/*IsGet=1表示已取机*/
62
' + @strWhr + '
63
GROUP BY Org_SimpleName, Product_Name, Product_ID
64
ORDER BY Org_SimpleName
65
) AS tmpTab
66
GROUP BY Org_SimpleName
67
WITH ROLLUP
68
')
69
GO
70![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
2
![](/Images/OutliningIndicators/None.gif)
3
![](/Images/OutliningIndicators/None.gif)
4
![](/Images/OutliningIndicators/None.gif)
5
![](/Images/OutliningIndicators/None.gif)
6
![](/Images/OutliningIndicators/None.gif)
7
![](/Images/OutliningIndicators/None.gif)
8
![](/Images/OutliningIndicators/None.gif)
9
![](/Images/OutliningIndicators/None.gif)
10
![](/Images/OutliningIndicators/None.gif)
11
![](/Images/OutliningIndicators/None.gif)
12
![](/Images/OutliningIndicators/None.gif)
13
![](/Images/OutliningIndicators/None.gif)
14
![](/Images/OutliningIndicators/None.gif)
15
![](/Images/OutliningIndicators/None.gif)
16
![](/Images/OutliningIndicators/None.gif)
17
![](/Images/OutliningIndicators/None.gif)
18
![](/Images/OutliningIndicators/None.gif)
19
![](/Images/OutliningIndicators/None.gif)
20
![](/Images/OutliningIndicators/None.gif)
21
![](/Images/OutliningIndicators/None.gif)
22
![](/Images/OutliningIndicators/None.gif)
23
![](/Images/OutliningIndicators/None.gif)
24
![](/Images/OutliningIndicators/None.gif)
25
![](/Images/OutliningIndicators/None.gif)
26
![](/Images/OutliningIndicators/None.gif)
27
![](/Images/OutliningIndicators/None.gif)
28
![](/Images/OutliningIndicators/None.gif)
29
![](/Images/OutliningIndicators/None.gif)
30
![](/Images/OutliningIndicators/None.gif)
31
![](/Images/OutliningIndicators/None.gif)
32
![](/Images/OutliningIndicators/None.gif)
33
![](/Images/OutliningIndicators/None.gif)
34
![](/Images/OutliningIndicators/None.gif)
35
![](/Images/OutliningIndicators/None.gif)
36
![](/Images/OutliningIndicators/None.gif)
37
![](/Images/OutliningIndicators/None.gif)
38
![](/Images/OutliningIndicators/None.gif)
39
![](/Images/OutliningIndicators/None.gif)
40
![](/Images/OutliningIndicators/None.gif)
41
![](/Images/OutliningIndicators/None.gif)
42
![](/Images/OutliningIndicators/None.gif)
43
![](/Images/OutliningIndicators/None.gif)
44
![](/Images/OutliningIndicators/None.gif)
45
![](/Images/OutliningIndicators/None.gif)
46
![](/Images/OutliningIndicators/None.gif)
47
![](/Images/OutliningIndicators/None.gif)
48
![](/Images/OutliningIndicators/None.gif)
49
![](/Images/OutliningIndicators/None.gif)
50
![](/Images/OutliningIndicators/None.gif)
51
![](/Images/OutliningIndicators/None.gif)
52
![](/Images/OutliningIndicators/None.gif)
53
![](/Images/OutliningIndicators/None.gif)
54
![](/Images/OutliningIndicators/None.gif)
55
![](/Images/OutliningIndicators/None.gif)
56
![](/Images/OutliningIndicators/None.gif)
57
![](/Images/OutliningIndicators/None.gif)
58
![](/Images/OutliningIndicators/None.gif)
59
![](/Images/OutliningIndicators/None.gif)
60
![](/Images/OutliningIndicators/None.gif)
61
![](/Images/OutliningIndicators/None.gif)
62
![](/Images/OutliningIndicators/None.gif)
63
![](/Images/OutliningIndicators/None.gif)
64
![](/Images/OutliningIndicators/None.gif)
65
![](/Images/OutliningIndicators/None.gif)
66
![](/Images/OutliningIndicators/None.gif)
67
![](/Images/OutliningIndicators/None.gif)
68
![](/Images/OutliningIndicators/None.gif)
69
![](/Images/OutliningIndicators/None.gif)
70
![](/Images/OutliningIndicators/None.gif)