求教SQL语句或加程序实现。(50分)

比如我有两个数据库的表。
twzb(id,wzname);主表
limeb(id,dates，price);子表

twzb表的内容为
id wzname
1 物资1
2 物资2
3 物资3
limeb表：
id dates price
1 01/01/2001 12.1
2 01/01/2001 13
2 01/02/2001 11.1
1 01/02/2001 22
3 01/01/2001 10
3 01/02/2001 3.2
2 01/03/2001 3.2
3 01/03/2001 3.4
1 01/03/2001 32

我想查询的结果是：
dates 物资1 物资2 物资3
2001-1-1 12.1 13 10
2001-1-2 22 11.1 3.2
2001-1-3 32 3.2 3.4

物资种类是不定的。是把原来的物资纵的改变成横向.
可以用SQL语句或加程序实现。如果实在不行，把物资种类定死也可以。
请各位写具体点，谢谢了。

用View 就可以了吗，在SQL Server 中创建一个View

学习

select dates,(select price from limeb where id=1 ) as 物资1，(select price from limeb where id=2 ) as 物资2，
(select price from limeb where id=3 ) as 物资3
from limeb
group by dates
order by dates

to :ugvanxk
我起先和你做的一样，但是有错误。不能通过。

VIEW，呵呵，可惜我还没有用过呢。

基本同意ugvanxk的
如果物资种类是不定的
即：
var
sSQL: String;
with Query do begin
SQL.Clear;
SQL.Add('Select * from twzb order by id');
Excusql;
while not eof do
begin
sSQL := sSQL + '(select price from limeb where id='+Inttostr(FieldByName('id').asinteger)
+' ) as '+FieldByName('wzName').asstring+',';
next;
end
end;
执行
select dates,sSQL from limeb
group by dates
order by dates

可以用SQL语句或加程序实现.你们前面我都做过，但是SQL语句有错误，错误原因是
select price from limeb where id=1 选出是多条的记录，如果这样
select TOP 1 price from limeb where id=1 SQL语句就能通过，但是结果是错的。
ID=2也同理。

我也不知怎么解决。先看看

select distinct(dates) from limb a
union all select
(select price from limeb c where id=1 and a.dates=c.dates
) as 物资1，(select price from limeb c where id=2 and a.dates=c.dates
) as 物资2，
(select price from limeb c where id=3 and a.dates=c.dates
) as 物资3
from limeb b where a.dates=b.dates
order by a.dates

select distinct(a.dates),b.物资1，c.物资2,d.物资3
from liemb a,(select price as 物资1 from bb where id=1 )as b,(select price as 物资1 from bb where id=2 )as c,
(select price as 物资1 from bb where id=3 )as d
where
a.dates=b.dates and
a.dates=c.dates and
a.dates=d.dates and
order by a.dates
没有sql server没法试，在access 中提示错误

wo: ugvanxk.很谢谢你。不过还是不行。错误原因一样

好了，下面这样
SELECT DISTINCT (WW.dates),
(SELECT price
FROM limeb
WHERE id = 1 AND ww.dates = dates) AS 物资1,
(SELECT price
FROM limeb
WHERE id = 2 AND ww.dates =dates) AS 物资2,
(SELECT price
FROM limeb
WHERE id = 3 AND ww.dates = dates) AS 物资3
FROM limeb WW
ORDER BY ww.dates

我也想和你的差不多
不过select 里有select语句好象不行，
改为
select distinct(dates),bb.price as 物资1，
from adt1 ww, (select price from adt1 b where id=1 and b.dates=ww.dates) as bb

select a.Dates,
a.Price as 物资一,
b.Price as 物资二,
c.Price as 物资三
from (select * From Limeb where id=1 )as a,
(select * From Limeb where id=2 ) as b,
(select * From Limeb where id=3 ) as c
where a.Dates=b.Dates
and b.Dates=C.Dates

我使用Delphi中的Query可以解決你的問題,下邊是我的程序
procedure TForm1.FormCreate(Sender: TObject);
var
i:integer;
SQL:TStringList;
SQL1,SQL2:String;
ID:integer;
begin
Query2.Open;
SQL:=TStringList.Create;
if Query2.RecordCount<1 then Exit;
if Query2.RecordCount=1 then
begin
Query1.Close;
Query1.SQl.Clear;
Query1.SQL.Add(format('select dates,price from limeb where id=%d ',[Query2.FieldByName('ID').AsInteger]));
Query1.Open;
end
else
begin
SQL1:='select ';
SQL2:='where ';
for i:=1 to Query2.RecordCount-1 do
begin
SQL.Add(format('(select dates,price from limeb where id=%d) A%d,',[Query2.FieldByName('id').AsInteger,Query2.FieldByName('id').AsInteger]));
if i=1 then
begin
SQL1:=SQL1+format('A%d.Dates,A%d.price,',[Query2.FieldByName('id').AsInteger,Query2.FieldByName('id').AsInteger]);
ID:=Query2.FieldByName('id').AsInteger;
end
else
SQL2:=SQL2+format('A%d.dates=A%d.dates(+) and ',[ID,Query2.FieldByName('id').AsInteger]);
SQL1:=SQL1+format('A%d.price,',[Query2.FieldByName('id').AsInteger]);
Query2.next;
end;
SQL.Add(format('(select dates,price from limeb where id=%d) A%d',[Query2.FieldByName('id').AsInteger,Query2.FieldByName('id').AsInteger]));
SQL1:=SQL1+format('A%d.price',[Query2.FieldByName('id').AsInteger]);
SQL2:=SQL2+format('A%d.dates=A%d.dates(+)',[ID,Query2.FieldByName('id').AsInteger]);
Query1.Close;
query1.SQL.Clear;
Query1.SQL.Add(SQL1);
Query1.SQL.Add('from');
for i:=0 to SQL.Count-1 do
begin
Query1.SQL.Add(SQL.Strings)
end;
Query1.SQL.Add(sql2);
Query1.OPen;

end;
//Memo1.Text:=Query1.SQL.Text;

end;

若需要物资种类为不定则需查询twzb动态生成查询脚本SQLScript：
var
SQLScript:String;
SelectStr:String;
FromStr:String;
WhereStr:String;
begin
AdoDataSet1.Close;
AdoDataSet1.CommandText:='select * from twzb';
AdoDataSet1.Open;
SelectStr:='select TT.Dates';
FromStr:='From (select Distinct(Dates) from Limeb) as TT';
While not AdoDataSet1.Eof do
begin
SelectStr:=SelectStr+',Tab'+AdoDataSet1.FieldByName('Id').AsString+'.Price as '+AdoDataSet1.FieldByName('wzname').AsString;
FromStr:=FromStr+',(select * From Limeb where id='+ AdoDataSet1.FieldByName('Id').AsString+')as Tab'+AdoDataSet1.FieldByName('Id').AsString;
if WhereStr='' then
WhereStr:='where TT.Dates=Tab'+AdoDataSet1.FieldByName('Id').AsString+'.Dates'
else
WhereStr:=WhereStr+' and '+'TT.Dates=Tab'+AdoDataSet1.FieldByName('Id').AsString+'.Dates';
AdoDataSet1.Next;
end;
AdoDataSet1.Close;
SQLScript:=SelectStr+' '+FromStr+' '+WhereStr;
end;

呵呵，下面几位，你们来晚了，我在前面已经得出正确的结果了。