4.9w+
社区成员
66.8w+
社区内容
- 主页
- ASP
- .NET Framework
- Web Services
- VB
- VC
- 图表区
- 分析与设计
- 组件/控件开发
- LINQ
如何生成这样的datatable 100分
如何把表一
id size color num
1 1 1 34
2 1 2 35
3 1 3 56
表二
colorid colorname
1 红
2 蓝
3 黄
在dataset中成datatable表三
size 红 蓝 黄
1 34 35 56
并希望能修改dataset时 改表一数据
id size color num
1 1 1 34
2 1 2 35
3 1 3 56
表二
colorid colorname
1 红
2 蓝
3 黄
在dataset中成datatable表三
size 红 蓝 黄
1 34 35 56
并希望能修改dataset时 改表一数据
...全文
zjjhfwh 2003年05月29日
up
zjjhfwh 2003年05月28日
up
zjjhfwh 2003年05月28日
declare @i int
declare @s varchar(300)
set @i = 1
set @s = 'select ImlId as 月份'
while exists (select * from xt_color where cast(xtColorID as int) = @i )
begin
select @s = @s + ',(case when cast(imlcolor as int) ='+ cast(@i as varchar) + ' then
imlnum else 0 end) as ' +xtColorName from xt_color where cast(xtColorID as int) = @i
set @i = @i + 1
end
set @s = @s + ' from gc_importlist order by ImlId'
exec (@s)
出错
declare @i int
declare @s varchar(300)
set @i =2
set @s = 'select ImlId as 月份'
while exists (select * from xt_color where cast(xtColorID as int) = @i )
begin
select @s = @s + ',(case when cast(imlcolor as int) ='+ cast(@i as varchar) + ' then
imlnum else 0 end) as ' +xtColorName from xt_color where cast(xtColorID as int) = @i
set @i = @i + 1
end
set @s = @s + ' from gc_importlist order by ImlId'
exec (@s)
set @i =1改成2就好了
什么原因??
declare @s varchar(300)
set @i = 1
set @s = 'select ImlId as 月份'
while exists (select * from xt_color where cast(xtColorID as int) = @i )
begin
select @s = @s + ',(case when cast(imlcolor as int) ='+ cast(@i as varchar) + ' then
imlnum else 0 end) as ' +xtColorName from xt_color where cast(xtColorID as int) = @i
set @i = @i + 1
end
set @s = @s + ' from gc_importlist order by ImlId'
exec (@s)
出错
declare @i int
declare @s varchar(300)
set @i =2
set @s = 'select ImlId as 月份'
while exists (select * from xt_color where cast(xtColorID as int) = @i )
begin
select @s = @s + ',(case when cast(imlcolor as int) ='+ cast(@i as varchar) + ' then
imlnum else 0 end) as ' +xtColorName from xt_color where cast(xtColorID as int) = @i
set @i = @i + 1
end
set @s = @s + ' from gc_importlist order by ImlId'
exec (@s)
set @i =1改成2就好了
什么原因??
luofix 2003年05月27日
在数据库总建立一个view。然后操作view
xiongliang2003 2003年05月27日
你那其实只与表1有关，表2 用来显示 。 只要取表1的数据，所以更新方面就应该方便些吧，
这是从MS的例子交叉表改的，不知道正确吗
SELECT size,
(CASE color WHEN 1 THEN Amount ELSE 0 END) AS 红,
(CASE color WHEN 2 THEN Amount ELSE 0 END) AS 蓝,
(CASE color WHEN 3 THEN Amount ELSE 0 END) AS 黄,
FROM xxxxxtable
GROUP BY size
这是从MS的例子交叉表改的，不知道正确吗
SELECT size,
(CASE color WHEN 1 THEN Amount ELSE 0 END) AS 红,
(CASE color WHEN 2 THEN Amount ELSE 0 END) AS 蓝,
(CASE color WHEN 3 THEN Amount ELSE 0 END) AS 黄,
FROM xxxxxtable
GROUP BY size
OK521 2003年05月27日
可以用datatable来做呀。但你写出的那两个表我看得不太懂。你说明白点，我帮你写。
feng105648 2003年05月27日
SqlConnection conn = GetConn();
string strQuery =@"SELECT dbo.[size].[size], dbo.color.colorname, dbo.[size].num FROM dbo.color INNER JOIN dbo.[size] ON dbo.color.colorid = dbo.[size].color ";
SqlDataAdapter da = new SqlDataAdapter(strQuery,conn);
DataSet ds = new DataSet();
da.Fill(ds);
int i=0;
string column1 = "";
string columns = "";
string values = "";
string value1="";
foreach(DataRow dr in ds.Tables[0].Rows)
{
if(i=0)
{
column1 = dr.Table.Columns[0].ColumnName;
value1 = dr[0].ToString();
columns = dr[1].ToString();
valuse = dr[2].ToString();
}
else
{
columns = columns+","+dr[1].ToString();
valuse = values+","+dr[2].ToString();
}
i++;
}
string[] temp = columns.Split(",");
string[] tempvalue = values.Split(",");
DataTable dt = new DataTable("Test");
dt.Columns.Add(column1);
for(int j=0;j<temp.Length;j++)
{
dt.Columns.Add(temp[j].ToString());
}
DataRow dr = dt.NewRow();
dr[0] = value1;
for(int k=0;k<tempvalue.Length;k++)
{
dr[k+1] = tempvalue[k].ToString();
}
string strQuery =@"SELECT dbo.[size].[size], dbo.color.colorname, dbo.[size].num FROM dbo.color INNER JOIN dbo.[size] ON dbo.color.colorid = dbo.[size].color ";
SqlDataAdapter da = new SqlDataAdapter(strQuery,conn);
DataSet ds = new DataSet();
da.Fill(ds);
int i=0;
string column1 = "";
string columns = "";
string values = "";
string value1="";
foreach(DataRow dr in ds.Tables[0].Rows)
{
if(i=0)
{
column1 = dr.Table.Columns[0].ColumnName;
value1 = dr[0].ToString();
columns = dr[1].ToString();
valuse = dr[2].ToString();
}
else
{
columns = columns+","+dr[1].ToString();
valuse = values+","+dr[2].ToString();
}
i++;
}
string[] temp = columns.Split(",");
string[] tempvalue = values.Split(",");
DataTable dt = new DataTable("Test");
dt.Columns.Add(column1);
for(int j=0;j<temp.Length;j++)
{
dt.Columns.Add(temp[j].ToString());
}
DataRow dr = dt.NewRow();
dr[0] = value1;
for(int k=0;k<tempvalue.Length;k++)
{
dr[k+1] = tempvalue[k].ToString();
}
feng105648 2003年05月27日
SqlConnection conn = GetConn();
string strQuery =@"SELECT dbo.[size].[size], dbo.color.colorname, dbo.[size].num FROM dbo.color INNER JOIN dbo.[size] ON dbo.color.colorid = dbo.[size].color ";
SqlDataAdapter da = new SqlDataAdapter(strQuery,conn);
DataSet ds = new DataSet();
da.Fill(ds);
int i=0;
string column1 = "";
string columns = "";
string values = "";
string value1="";
foreach(DataRow dr in ds.Tables[0].Rows)
{
if(i=0)
{
column1 = dr.Table.Columns[0].ColumnName;
value1 = dr[0].ToString();
columns = dr[1].ToString();
valuse = dr[2].ToString();
}
else
{
columns = columns+","+dr[1].ToString();
valuse = values+","+dr[2].ToString();
}
i++;
}
string[] temp = columns.Split(",");
string[] tempvalue = values.Split(",");
DataTable dt = new DataTable("Test");
dt.Columns.Add(column1);
for(int j=0;j<temp.Length;j++)
{
dt.Columns.Add(temp[j].ToString());
}
DataRow dr = dt.NewRow();
dr[0] = value1;
for(int k=0;k<dt.Columns.Count;k++)
{
dr[k+1] = tempvalue[k].ToString();
}
string strQuery =@"SELECT dbo.[size].[size], dbo.color.colorname, dbo.[size].num FROM dbo.color INNER JOIN dbo.[size] ON dbo.color.colorid = dbo.[size].color ";
SqlDataAdapter da = new SqlDataAdapter(strQuery,conn);
DataSet ds = new DataSet();
da.Fill(ds);
int i=0;
string column1 = "";
string columns = "";
string values = "";
string value1="";
foreach(DataRow dr in ds.Tables[0].Rows)
{
if(i=0)
{
column1 = dr.Table.Columns[0].ColumnName;
value1 = dr[0].ToString();
columns = dr[1].ToString();
valuse = dr[2].ToString();
}
else
{
columns = columns+","+dr[1].ToString();
valuse = values+","+dr[2].ToString();
}
i++;
}
string[] temp = columns.Split(",");
string[] tempvalue = values.Split(",");
DataTable dt = new DataTable("Test");
dt.Columns.Add(column1);
for(int j=0;j<temp.Length;j++)
{
dt.Columns.Add(temp[j].ToString());
}
DataRow dr = dt.NewRow();
dr[0] = value1;
for(int k=0;k<dt.Columns.Count;k++)
{
dr[k+1] = tempvalue[k].ToString();
}
CMIC 2003年05月27日
DataSet myDs=new DataSet();
DataTable myDt=new DataTable();
myDt.Columns.Add("size");
myDt.Columns.Add("红");
myDt.Columns.Add("蓝");
myDt.Columns.Add("黄");
SqlDataAdapter Da=new SqlDataAdapter("select tsize,colorname,num from table1,table2 where table1.id=table2.colorid",myCn);
DataTable Dt=new DataTable();
Da.Fill(Dt);
int i=0;
DataRow myDr=myDt.NewRow();
foreach(DataRow Dr in Dt.Rows)
{
i++;
if(i==1)
{
myDr["size"]=Dr["size"];
myDr["红"]=Dr["num"];
}
if(i==2)
{
myDr["蓝"]=Dr["num"];
}
if(i==3)
{
i=0;
DataRow myDr=myDt.NewRow();
myDr["黄"]=Dr["num"];
}
}
myDs.Tables.Add(myDt);
下班了急,请大家不要笑话我语法的不规则.
DataTable myDt=new DataTable();
myDt.Columns.Add("size");
myDt.Columns.Add("红");
myDt.Columns.Add("蓝");
myDt.Columns.Add("黄");
SqlDataAdapter Da=new SqlDataAdapter("select tsize,colorname,num from table1,table2 where table1.id=table2.colorid",myCn);
DataTable Dt=new DataTable();
Da.Fill(Dt);
int i=0;
DataRow myDr=myDt.NewRow();
foreach(DataRow Dr in Dt.Rows)
{
i++;
if(i==1)
{
myDr["size"]=Dr["size"];
myDr["红"]=Dr["num"];
}
if(i==2)
{
myDr["蓝"]=Dr["num"];
}
if(i==3)
{
i=0;
DataRow myDr=myDt.NewRow();
myDr["黄"]=Dr["num"];
}
}
myDs.Tables.Add(myDt);
下班了急,请大家不要笑话我语法的不规则.
zjjhfwh 2003年05月27日
up
zjjhfwh 2003年05月27日
up
mongtze 2003年05月27日
sql 语句嵌套完全可以实现。
lds88 2003年05月27日
你到sqlserver版搜“交叉报表”，相信会得到满意的答案
zjjhfwh 2003年05月27日
急啊！！救救我。