我正在尝试在Gridview中添加明智的列,但不能。
像我填写gridview从存储过程,它给出这些值。
DateTime 10Quantity 20Quantity 30Quantity 10Amount 20Amount 30Amount GrandTotal 03/10/2015 792 0 594 7920 0 17820 25740 03/10/2015 332 3 194 9990 0 28220 38739 预计: DateTime 10Quantity 20Quantity 30数量10A安装20安装30安装总数 03/10/2015 792 0 594 7920 0 17820 25740 03/10/2015 332 3 194 9990 0 28220 38739 总计1124 3 788 17910 0 46040 64479我想要一个新的行'Totals',它应该表示每列的总数,即加法。
$ b代码: pre $ lt; code>< asp:GridView ID =GridViewDenominationsWiseTransactionsrunat =小号erverwidth =100%AutoGenerateColumns =False ShowFooter =TrueOnRowDataBound =GridViewDenominationsWiseTransactions_RowDataBoundCssClass =table table-hover table-striped table-bordered> <列> < asp:BoundField HeaderText =DateDataField =DateTimeDataFormatString ={0:dd / MMM / yyyy hh:mm}HtmlEncode =falseItemStyle-CssClass =visible-desktop HeaderStyle-CssClass =可见桌面> < HeaderStyle CssClass =visible-desktop>< / HeaderStyle> < ItemStyle Width =11%/> < / asp:BoundField> <% - < asp:BoundField HeaderText =Conductor NameDataField =ConductorNameItemStyle-CssClass =visible-desktop HeaderStyle-CssClass =visible-desktop> < HeaderStyle CssClass =visible-desktop>< / HeaderStyle> < ItemStyle Width =8%>< / ItemStyle> < / asp:BoundField> - %> < asp:BoundField HeaderText =10 QuantityDataField =10QuantityItemStyle-CssClass =visible-desktop HeaderStyle-CssClass =visible-desktop> < HeaderStyle CssClass =visible-desktop>< / HeaderStyle> < ItemStyle Width =8%CssClass =visible-desktop>< / ItemStyle> < / asp:BoundField> < asp:BoundField HeaderText =20 QuantityDataField =20QuantityItemStyle-CssClass =visible-desktop HeaderStyle-CssClass =visible-desktop> < HeaderStyle CssClass =visible-desktop>< / HeaderStyle> < ItemStyle Width =8%CssClass =visible-desktop>< / ItemStyle> < / asp:BoundField> < asp:BoundField HeaderText =30 QuantityDataField =30QuantityItemStyle-CssClass =visible-desktop HeaderStyle-CssClass =visible-desktop> < HeaderStyle CssClass =visible-desktop>< / HeaderStyle> < ItemStyle Width =8%CssClass =visible-desktop>< / ItemStyle> < / asp:BoundField> < asp:BoundField HeaderText =10 AmountDataField =10AmountItemStyle-CssClass =visible-desktop HeaderStyle-CssClass =visible-desktop> < HeaderStyle CssClass =visible-desktop>< / HeaderStyle> < ItemStyle Width =8%CssClass =visible-desktop>< / ItemStyle> < / asp:BoundField> < asp:BoundField HeaderText =20 AmountDataField =20AmountItemStyle-CssClass =visible-desktop HeaderStyle-CssClass =visible-desktop> < HeaderStyle CssClass =visible-desktop>< / HeaderStyle> < ItemStyle Width =8%CssClass =visible-desktop>< / ItemStyle> < / asp:BoundField> < asp:BoundField HeaderText =30 AmountDataField =30AmountItemStyle-CssClass =visible-desktop HeaderStyle-CssClass =visible-desktop> < HeaderStyle CssClass =visible-desktop>< / HeaderStyle> < ItemStyle Width =8%CssClass =visible-desktop>< / ItemStyle> < / asp:BoundField> < asp:BoundField HeaderText =GrandTotalDataField =GrandTotalItemStyle-CssClass =visible-desktop HeaderStyle-CssClass =visible-desktop> < HeaderStyle CssClass =visible-desktop>< / HeaderStyle> < ItemStyle Width =13%CssClass =visible-desktop>< / ItemStyle> < / asp:BoundField> < /列> < / asp:GridView>
SP;
ALTER PROCEDURE [dbo]。[ReportDenominationWiseTransaction] '03 -10-2015','03 -10-2015' @FromDate date, @ToDate date AS $ 选择转换(varchar(20),serverdatetime,101)'DateTime',金额,1'Quantity' from票 WHERE CONVERT(DATE,ServerDateTime)BETWEEN @FromDate和@ToDate ) select *,10 * c。[10Quantity]'10Amount' (20×数量[20数量])+(20 *数量[20数量])+(30 * c。[20数量]'20Amount',30 * c。[30数量]'30Amount' 30Quantity',[30] AS'30Quantity' FROM (SELECT [30]))'GrandTotal'( SELECT DateTime,[10] AS'10Quantity',[20] [日期时间],金额,数量 FROM myquery)p PIVOT ( COUNT([Quantity]) FOR [Amount] IN ([10],[20],[30]))AS pvt )as c END我必须从前端完成,即gridview
解决方案实际上你可以通过 sql找到每列的 SUM / code>本身使用 GROUPING SETS 。
查询
SELECT [DateTime], SUM([10Quantity])AS [10Quantity], SUM([20Quantity] )AS [20Quantity], SUM([30Quantity])AS [20Quantity], SUM([10Amount])AS [10Amount], SUM([20Amount])AS [20Amount] , SUM([30Amount])AS [30Amount], SUM([GrandTotal])AS [GrandTotal] FROM table_name GROUP BY GROUPING SETS(( [DateTime],[10Quantity],[20Quantity], [30Quantity],[10Amount],[20Amount],[30Amount],[GrandTotal]),());
i am trying to do column wise addition in Gridview but couldn't.
like i am filling gridview from store procedure which gives these values .
DateTime 10Quantity 20Quantity 30Quantity 10Amount 20Amount 30Amount GrandTotal 03/10/2015 792 0 594 7920 0 17820 25740 03/10/2015 332 3 194 9990 0 28220 38739Expected:
DateTime 10Quantity 20Quantity 30Quantity 10Amount 20Amount 30Amount GrandTotal 03/10/2015 792 0 594 7920 0 17820 25740 03/10/2015 332 3 194 9990 0 28220 38739 Totals 1124 3 788 17910 0 46040 64479i want a new row 'Totals' which should represent total for each column i.e. addition.
Code:
<asp:GridView ID="GridViewDenominationsWiseTransactions" runat="server" Width="100%" AutoGenerateColumns="False" ShowFooter="True" OnRowDataBound="GridViewDenominationsWiseTransactions_RowDataBound" CssClass="table table-hover table-striped table-bordered"> <Columns> <asp:BoundField HeaderText="Date" DataField="DateTime" DataFormatString="{0:dd/MMM/yyyy hh:mm}" HtmlEncode="false" ItemStyle-CssClass="visible-desktop" HeaderStyle-CssClass="visible-desktop"> <HeaderStyle CssClass="visible-desktop"></HeaderStyle> <ItemStyle Width="11%" /> </asp:BoundField> <%-- <asp:BoundField HeaderText="Conductor Name" DataField="ConductorName" ItemStyle-CssClass="visible-desktop" HeaderStyle-CssClass="visible-desktop"> <HeaderStyle CssClass="visible-desktop"></HeaderStyle> <ItemStyle Width="8%"></ItemStyle> </asp:BoundField>--%> <asp:BoundField HeaderText="10 Quantity" DataField="10Quantity" ItemStyle-CssClass="visible-desktop" HeaderStyle-CssClass="visible-desktop"> <HeaderStyle CssClass="visible-desktop"></HeaderStyle> <ItemStyle Width="8%" CssClass="visible-desktop"></ItemStyle> </asp:BoundField> <asp:BoundField HeaderText="20 Quantity" DataField="20Quantity" ItemStyle-CssClass="visible-desktop" HeaderStyle-CssClass="visible-desktop"> <HeaderStyle CssClass="visible-desktop"></HeaderStyle> <ItemStyle Width="8%" CssClass="visible-desktop"></ItemStyle> </asp:BoundField> <asp:BoundField HeaderText="30 Quantity" DataField="30Quantity" ItemStyle-CssClass="visible-desktop" HeaderStyle-CssClass="visible-desktop"> <HeaderStyle CssClass="visible-desktop"></HeaderStyle> <ItemStyle Width="8%" CssClass="visible-desktop"></ItemStyle> </asp:BoundField> <asp:BoundField HeaderText="10 Amount" DataField="10Amount" ItemStyle-CssClass="visible-desktop" HeaderStyle-CssClass="visible-desktop"> <HeaderStyle CssClass="visible-desktop"></HeaderStyle> <ItemStyle Width="8%" CssClass="visible-desktop"></ItemStyle> </asp:BoundField> <asp:BoundField HeaderText="20 Amount" DataField="20Amount" ItemStyle-CssClass="visible-desktop" HeaderStyle-CssClass="visible-desktop"> <HeaderStyle CssClass="visible-desktop"></HeaderStyle> <ItemStyle Width="8%" CssClass="visible-desktop"></ItemStyle> </asp:BoundField> <asp:BoundField HeaderText="30 Amount" DataField="30Amount" ItemStyle-CssClass="visible-desktop" HeaderStyle-CssClass="visible-desktop"> <HeaderStyle CssClass="visible-desktop"></HeaderStyle> <ItemStyle Width="8%" CssClass="visible-desktop"></ItemStyle> </asp:BoundField> <asp:BoundField HeaderText="GrandTotal" DataField="GrandTotal" ItemStyle-CssClass="visible-desktop" HeaderStyle-CssClass="visible-desktop"> <HeaderStyle CssClass="visible-desktop"></HeaderStyle> <ItemStyle Width="13%" CssClass="visible-desktop"></ItemStyle> </asp:BoundField> </Columns> </asp:GridView>SP;
ALTER PROCEDURE [dbo].[ReportDenominationWiseTransaction] '03-10-2015', '03-10-2015' @FromDate date, @ToDate date AS BEGIN with myquery ([DateTime],amount,Quantity) as ( select Convert(varchar(20),serverdatetime,101) 'DateTime',amount,1 'Quantity' from tickets WHERE CONVERT(DATE,ServerDateTime) BETWEEN @FromDate and @ToDate ) select *, 10 * c.[10Quantity] '10Amount',20 * c.[20Quantity] '20Amount',30 * c.[30Quantity] '30Amount',((10 * c.[10Quantity])+(20 * c.[20Quantity])+(30 * c.[30Quantity]))'GrandTotal' from ( SELECT DateTime,[10] AS '10Quantity', [20] AS '20Quantity', [30] AS '30Quantity' FROM (SELECT [DateTime], amount,quantity FROM myquery) p PIVOT ( COUNT ([Quantity]) FOR [Amount] IN ( [10], [20], [30]) ) AS pvt ) as c ENDi must do it from front end i.e. gridview
解决方案Actually you can find the SUM of each column by sql itself using GROUPING SETS.
Query
SELECT [DateTime], SUM([10Quantity]) AS [10Quantity], SUM([20Quantity]) AS [20Quantity], SUM([30Quantity]) AS [30Quantity], SUM([10Amount]) AS [10Amount], SUM([20Amount]) AS [20Amount], SUM([30Amount]) AS [30Amount], SUM([GrandTotal]) AS [GrandTotal] FROM table_name GROUP BY GROUPING SETS(([DateTime],[10Quantity],[20Quantity], [30Quantity],[10Amount],[20Amount],[30Amount],[GrandTotal]),());
更多推荐
如何在sql中产生每列的总和?
发布评论