如何用SQL语句实现数据表行列的转换(100分)

如：YMD DL_001 DL_002 DL_003 DL_004
20080401 123 456 567 789
20080402 234 345 567 123
..................
要求转换后得到：
YMD 20080401 20080402 ......
DL_001 123 234 .....
DL_002 456 345 .....
DL_003 567 567 .....
DL_004 789 123 .....
创建测试表：
--创建测试表
create table test(YMD varchar(10)
,[DL_001] INT
,[DL_002] INT
,[DL_003] INT
,[DL_004] INT)
insert test select '20080401','111','222','333','444'
union all select '20080402','444','555','666','565'
union all select '20080403','777','888','999','356'
go

请大虾们帮我解决一下，最好给出详细的语句，谢过了！！！

这种活儿以前经常做,我的做法很笨,创一个存贮过程,用游标创建一个临时表,然后往里面插数据.

如果你的数据很有规则, 建议你取回到客户端再转换,可以省不少时间. 而且转换的代码很容易通用的.

问题还没有解决，哪位朋友帮忙解决一下！谢谢了

如果只是为了直观显示，不必采用SQL语句，有一个专门的DBGRID控件可处理上述问题，
DBVGrids4,可在网上搜到!

典型的行列转换：
select YMD,isnull((select sum(DL_001) from test where YMD=A.YMD group by YMD),0) as DL_001,isnull((select sum(DL_002) from test where YMD=A.YMD group by YMD),0) as DL_002,isnull((select sum(DL_003) from test where YMD=A.YMD group by YMD),0) as DL_003,isnull((select sum(DL_004) from test where YMD=A.YMD group by YMD),0) as DL_004 from test A group by YMD
如果有条件，在中间加上条件

declare @a table (YMD varchar(10)
,[DL_001] INT
,[DL_002] INT
,[DL_003] INT
,[DL_004] INT)
insert @a select '20080401','111','222','333','444'
union all select '20080402','444','555','666','565'
union all select '20080403','777','888','999','356'
select * from @a

declare
@str varchar(2000),
@YMD varchar(20),
@DL01 int,
@DL02 int,
@DL03 int,
@DL04 int


declare cur_a scroll cursor for select YMD,DL_001,DL_002,DL_003,DL_004 from @a
open cur_a
fetch first from cur_a into @YMD,@DL01,@DL02,@DL03,@DL04

set @str='declare @b table([DL] varchar(10)'
while @@fetch_status=0 begin
set @str=@str+',['+@YMD+'] int'
fetch cur_a into @YMD,@DL01,@DL02,@DL03,@DL04
end

set @str=@str+') insert @b(DL) values(''DL_001'') insert @b(DL) values(''DL_002'')
insert @b(DL) values(''DL_003'') insert @b(DL) values(''DL_004'') '

fetch first from cur_a into @YMD,@DL01,@DL02,@DL03,@DL04
while @@fetch_status=0 begin
set @str=@str+' update @b set ['+@YMD+']='+cast(@DL01 as varchar)+' where DL=''DL_001'' '
set @str=@str+' update @b set ['+@YMD+']='+cast(@DL02 as varchar)+' where DL=''DL_002'' '
set @str=@str+' update @b set ['+@YMD+']='+cast(@DL03 as varchar)+' where DL=''DL_003'' '
set @str=@str+' update @b set ['+@YMD+']='+cast(@DL04 as varchar)+' where DL=''DL_004'' '
fetch cur_a into @YMD,@DL01,@DL02,@DL03,@DL04
end
set @str=@str+' select * from @b'

close cur_a
deallocate cur_a

print(@str)
exec(@str)

多人接受答案了。