请教这样的统计如何实现？
请教各位：我想把系统的所有的商户按一年12个月数据分别统计出来 如何实现 ？如：
商户 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
家乐福 122 200 300 300 299 289 22 33 66 99 200 456
..... --------------------编程问答-------------------- 参考：
http://www.cnblogs.com/insus/articles/1969896.html
http://www.cnblogs.com/insus/articles/1970577.html
http://www.cnblogs.com/insus/articles/1970707.html
http://www.cnblogs.com/insus/articles/1971446.html
--------------------编程问答-------------------- 支持楼上 --------------------编程问答-------------------- 行专列 --------------------编程问答-------------------- 行列转换，例子很多，
http://hi.baidu.com/qzrc/blog/item/952686184634e0b44aedbcea.html --------------------编程问答--------------------
SELECT [name], [01月] AS [1月], [02月] AS [2月], [03月] AS [3月], [04月] AS [4月],[05月] as [5月],[06月] as [6月],[07月] as [7月],[08月] as [8月],[09月] as [9月],[10月] as [10月],[11月] as [11月],[12月] as [12月]
FROM
(SELECT [count], (datename(mm,date) +'月') as date, [name]
FROM test where datename(yyyy,date)='2011') p
PIVOT
(
sum ([count]) FOR date IN( [01月],[02月],[03月],[04月],[05月],[06月],[07月],[08月],[09月],[10月],[11月],[12月] )
) AS pvt
ORDER BY [name];
--------------------编程问答--------------------
行转列哟 ls+1哟
--------------------编程问答--------------------
直接 用视图 输出 或者在后台的数据源 直接 给赋值 --------------------编程问答--------------------
+1
通过wknight_it的回复修改的 看那个链接就懂了
Declare @sql varchar(8000)--------------------编程问答-------------------- 谢谢各位 ，本人实在才疏学浅 ，麻烦帮我写一下这个SQL语句 ，表名为Validation(SysID,CustID,ValidDate,RouteID,VerifyNum,Remark),根据ValidDate字段统计出每个月VerifyNum大于0或等于0 --------------------编程问答-------------------- 顶一下 ！ --------------------编程问答-------------------- <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" Width="685px" Height="145px">
Set @sql = 'Select 商户'
Select @sql = @sql + ',sum(case Subject when '''+月份+''' then Result else 0 end) ['+月份+']'
from (select distinct 月份 from 表) as 表2
Select @sql = @sql+' from 表 group by 商户'
Exec (@sql)
<Columns>
<asp:BoundField HeaderText="" DataField="yue"/>
<asp:BoundField HeaderText="合计" DataField="合计" DataFormatString="{0:n2}"/>
<asp:BoundField HeaderText="一月" DataField="1"/>
<asp:BoundField HeaderText="二月" DataField="2" />
<asp:BoundField HeaderText="三月" DataField="3" />
<asp:BoundField HeaderText="四月" DataField="4"/>
<asp:BoundField HeaderText="五月" DataField="5" />
<asp:BoundField HeaderText="六月" DataField="6" />
<asp:BoundField HeaderText="七月" DataField="7" />
<asp:BoundField HeaderText="八月" DataField="8" />
<asp:BoundField HeaderText="九月" DataField="9" />
<asp:BoundField HeaderText="十月" DataField="10" />
<asp:BoundField HeaderText="十一月" DataField="11" />
<asp:BoundField HeaderText="十二月" DataField="12" />
</Columns>
</asp:GridView>
public static DataTable Col2Row(DataTable src, int columnHead)
{
DataTable result = new DataTable();
DataColumn myHead = src.Columns[columnHead];
result.Columns.Add(myHead.ColumnName);
for (int i = 0; i < src.Rows.Count; i++)
{
result.Columns.Add(src.Rows[i][myHead].ToString());
}
foreach (DataColumn col in src.Columns)
{
if (col == myHead)
continue;
object[] newRow = new object[src.Rows.Count + 1];
newRow[0] = col.ColumnName;
for (int i = 0; i < src.Rows.Count; i++)
{ newRow[i + 1] = src.Rows[i][col]; } result.Rows.Add(newRow);
}
return result;
}
public static DataTable Col2Row(DataTable src, string columnHead)
{
for (int i = 0; i < src.Columns.Count; i++)
{
if (src.Columns[i].ColumnName.ToUpper() == columnHead.ToUpper())
return Col2Row(src, i);
}
return new DataTable();
}
--------------------编程问答-------------------- 说明一下
--------------------编程问答-------------------- 行转列，列转行的例子很多，网上一找一大堆，不管是sql的还是程序里的都有 --------------------编程问答--------------------
这是前台代码
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" Width="685px" Height="145px">
<Columns>
<asp:BoundField HeaderText="" DataField="yue"/>
<asp:BoundField HeaderText="合计" DataField="合计" DataFormatString="{0:n2}"/>
<asp:BoundField HeaderText="一月" DataField="1"/>
<asp:BoundField HeaderText="二月" DataField="2" />
<asp:BoundField HeaderText="三月" DataField="3" />
<asp:BoundField HeaderText="四月" DataField="4"/>
<asp:BoundField HeaderText="五月" DataField="5" />
<asp:BoundField HeaderText="六月" DataField="6" />
<asp:BoundField HeaderText="七月" DataField="7" />
<asp:BoundField HeaderText="八月" DataField="8" />
<asp:BoundField HeaderText="九月" DataField="9" />
<asp:BoundField HeaderText="十月" DataField="10" />
<asp:BoundField HeaderText="十一月" DataField="11" />
<asp:BoundField HeaderText="十二月" DataField="12" />
</Columns>
</asp:GridView>
这是行列转换的代码：
public static DataTable Col2Row(DataTable src, int columnHead)
{
DataTable result = new DataTable();
DataColumn myHead = src.Columns[columnHead];
result.Columns.Add(myHead.ColumnName);
for (int i = 0; i < src.Rows.Count; i++)
{
result.Columns.Add(src.Rows[i][myHead].ToString());
}
foreach (DataColumn col in src.Columns)
{
if (col == myHead)
continue;
object[] newRow = new object[src.Rows.Count + 1];
newRow[0] = col.ColumnName;
for (int i = 0; i < src.Rows.Count; i++)
{ newRow[i + 1] = src.Rows[i][col]; } result.Rows.Add(newRow);
}
return result;
}
public static DataTable Col2Row(DataTable src, string columnHead)
{
for (int i = 0; i < src.Columns.Count; i++)
{
if (src.Columns[i].ColumnName.ToUpper() == columnHead.ToUpper())
return Col2Row(src, i);
}
return new DataTable();
}
SELECT [name], [01月] AS [1月], [02月] AS [2月], [03月] AS [3月], [04月] AS [4月],[05月] as [5月],[06月] as [6月],[07月] as [7月],[08月] as [8月],[09月] as [9月],[10月] as [10月],[11月] as [11月],[12月] as [12月]
FROM
(SELECT [count], (datename(mm,date) +'月') as date, [name]
FROM test where datename(yyyy,date)='2011') p
PIVOT
(
sum ([count]) FOR date IN( [01月],[02月],[03月],[04月],[05月],[06月],[07月],[08月],[09月],[10月],[11月],[12月] )
) AS pvt
ORDER BY [name];
--------------------编程问答--------------------
--------------------编程问答--------------------
这个问题上个月刚出现过一次14#已经给出答案了
http://topic.csdn.net/u/20110817/11/289d83e0-6cf9-44a0-8116-94616123a05d.html
http://topic.csdn.net/u/20110820/10/99cb8790-342c-497d-99e7-251751dbab48.html --------------------编程问答--------------------
补充：.NET技术 , ASP.NET
