求SQL语句，特殊的行转列

[wing013]

create table tmpTable
(supplierid varchar(2),arrivaldate varchar(8),times varchar(1),qty int)

insert tmpTable
select '01','20090101','1',10 union all
select '02','20090102','2',20 union all
select '05','20090102','3',30 union all
select '03','20090101','2',20 union all
select '04','20090102','2',20


--日期不是固定的,可能每天都有
--其中times 1表示上午 2表示下午 3表示晚上
--此表为供应商送货表，现在的是想找出每个日期里，那些供应商送了什么货来
--找出的结果如下表

20090101 20090102
supplierid times qty supplierid times qty
01 1 10 02 2 20
03 2 30 05 3 30
04 2 20

[liangCK]

结果太整齐了..

[wing013]

--此表为供应商送货表，现在的是想找出每个日期里，那些供应商送了什么货来
20090101 20090102
supplierid times qty supplierid times qty
01 1 10 02 2 20
03 2 30 05 3 30
04 2 20

[昵称被占用了]

如果日期多，这个报表将很宽，所以怀疑其实用性，不想花精力去写

[feixianxxx]

看不懂那结果。。。

[yjfjebj789]

declare @SQL varchar(8000)
set @SQL=''
select @SQL=@SQL+',(case when arrivaldate='''+arrivaldate+''' then supplierid else '''' end)as [ ],
(case when arrivaldate='''+arrivaldate+''' then times else '''' end)as ['+arrivaldate+'],
(case when arrivaldate='''+arrivaldate+''' then cast(qty as varchar) else '' '' end)as [ ]'
from tmpTable group by arrivaldate
set @SQL=substring(@SQL,2,len(@SQL))
print @SQL
exec('select '+@SQL+' from tmpTable')

[csdyyr]

create   table   tmpTable  
(supplierid   varchar(2),arrivaldate   varchar(8),times   varchar(1),qty   int)

insert   tmpTable  
select   '01 ', '20090101 ', '1 ',10   union   all
select   '02 ', '20090102 ', '2 ',20   union   all
select   '05 ', '20090102 ', '3 ',30   union   all
select   '03 ', '20090101 ', '2 ',20   union   all
select   '04 ', '20090102 ', '2 ',20   

select *,id=identity(int,1,1) into # from tmpTable order by arrivaldate,supplierid

declare @str varchar(8000)
set @str=''
select @str=@str+',max(case when arrivaldate='''+arrivaldate+''' and seq=tb.seq then supplierid else '''' end) as supplierid,
				   max(case when arrivaldate='''+arrivaldate+''' and seq=tb.seq then times else '''' end) as times,
				   sum(case when arrivaldate='''+arrivaldate+''' then qty else 0 end) as qty'
from tmpTable
group by arrivaldate
order by arrivaldate
	 
set @str=stuff(@str,1,1,'')
exec('select '+@str+' from (select *,seq=id-(select count(*) from # where arrivaldate<ta.arrivaldate) from # as ta) tb group by seq')

drop table tmpTable
drop table #
/*
supplierid times qty         supplierid times qty
---------- ----- ----------- ---------- ----- -----------
01         1     10          02         2     20
03         2     20          04         2     20
                 0           05         3     30
*/

[yjfjebj789]

只能实现这样
20090101 20090102
01 - - - - -
1 1 - - - -
- - - 02 2 2
- - 05 3 3 03
2 2 - - - -
- - 04 2 2 -

或者你用分组求和或者什么的

[yjfjebj789]

如果分组求和的话, 那你的同一天有多个商品怎么出来??

[ChinaJiaBing]

if object_id('tmpTable')is not null
   drop table tmpTable
 go
create   table   tmpTable   
(supplierid   varchar(2),arrivaldate   varchar(8),times   varchar(1),qty   int) 

insert   tmpTable   
select   '01 ', '20090101 ', '1 ',10   union   all 
select   '02 ', '20090102 ', '2 ',20   union   all 
select   '05 ', '20090102 ', '3 ',30   union   all 
select   '03 ', '20090101 ', '2 ',20   union   all 
select   '04 ', '20090102 ', '2 ',20   
go
select a.supplierid,a.times,a.qty,b.supplierid,b.times,b.qty from 
(select ROW_NUMBER()over(order by supplierid) 序号,  supplierid,times,qty from tmpTable where arrivaldate='20090101') a full join 
(select ROW_NUMBER()over(order by supplierid) 序号, supplierid,times,qty from tmpTable where arrivaldate='20090102') b  on a.序号=b.序号




(5 行受影响)
supplierid times qty         supplierid times qty
---------- ----- ----------- ---------- ----- -----------
01         1     10          02         2     20
03         2     20          04         2     20
NULL       NULL  NULL        05         3     30

(3 行受影响)