如何生成这样的datatable 100分

[zjjhfwh]

如何把表一
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]

急啊！！救救我。

[lds88]

你到sqlserver版搜“交叉报表”，相信会得到满意的答案

[mongtze]

sql 语句嵌套完全可以实现。

[zjjhfwh]

up

[zjjhfwh]

up

[CMIC]

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);
下班了急,请大家不要笑话我语法的不规则.

[feng105648]

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();
}

[feng105648]

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();
}

[OK521]

可以用datatable来做呀。但你写出的那两个表我看得不太懂。你说明白点，我帮你写。

[xiongliang2003]

你那其实只与表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

[luofix]

在数据库总建立一个view。然后操作view

[zjjhfwh]

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就好了


什么原因??

[zjjhfwh]

up

[zjjhfwh]

up