SQL 问题

[xuedaniel]

YP表

xf_date youpin qty
2004-08-01 90# 80
2004-08-02 93# 70
2004-08-02 90# 20
2004-08-03 90# 60
2004-08-04 97# 50
2004-08-05 0# 40
2004-08-07 93# 30

如何得到如下结果:
90# 93# 97# 0#
2004-08-01 80 70
2004-08-02 20
2004-08-03 60
2004-08-04 50
2004-08-05 40
2004-08-07 30

须按xf_date排列，youpin项目不受限制（不止90#、93#、97#、0#，可能还有其它）

那位能帮忙解决的，不胜感激。

QQ：48158286
MAIL：[e-mail]

[giggsbj]

select xf_date, qty as 90# ,null as 93# ,null as 97# ,null as 0#
union
select xf_date, null as 90# ,qty as 93# ,null as 97# ,null as 0#
union
select xf_date, null as 90# ,null as 93# ,qty as 97# ,null as 0#
union
select xf_date, null as 90# ,null as 93# ,null as 97# ,qty as 0#

[yjzhg]

交叉表，搜一下以前的贴子

[futulove]

动态语句
以前这样的贴子有的是
搜一下参考一下就可以了

[LoveSQL]

create table tb (xf_date datetime, youpin varchar(20), qty int)
insert into tb select
'2004-08-01','90#', 80 union all select
'2004-08-02','93#', 70 union all select
'2004-08-02','90#', 20 union all select
'2004-08-03','90#', 60 union all select
'2004-08-04','97#', 50 union all select
'2004-08-05','0#', 40 union all select
'2004-08-07','93#', 30

--测试

declare @str varchar(4000)
set @str=''
select @str=@str+','+'sum(case youpin when '''+a.youpin+''' then qty else 0 end ) ['+a.youpin+']'
from (select distinct youpin from tb) a

exec('select xf_date'+@str+' from tb group by xf_date order by xf_date')

drop table tb
--结果
xf_date 0# 90# 93# 97#
------------------------------------------------------ ----------- ----------- ----------- -----------
2004-08-01 00:00:00.000 0 80 0 0
2004-08-02 00:00:00.000 0 20 70 0
2004-08-03 00:00:00.000 0 60 0 0
2004-08-04 00:00:00.000 0 0 0 50
2004-08-05 00:00:00.000 40 0 0 0
2004-08-07 00:00:00.000 0 0 30 0