纵转横，新问题。

[smile9961]

以下分别为编号是员工9001、9002借还书的纪录：

sNo borrow repay odate
---------- ----------- ----------- -----------------------
9001 10 1 2006-01-01 00:00:00.000
9001 12 2 2006-01-02 00:00:00.000
9001 13 3 2006-01-03 00:00:00.000
9002 18 7 2006-01-01 00:00:00.000
9002 19 8 2006-01-02 00:00:00.000
9002 20 9 2006-01-03 00:00:00.000

现在想将这些数据转为横向显示，即：

sNo 2006-01-01 2006-01-02 2006-01-03
9001 10 12 13 --9001's borrow
9001 1 2 3 --9001's repay
9002 18 19 20 --9002's borrow
9002 7 8 9 --9002's repay

请大家帮忙看看，测试数据如下：

create table #
(
sNo varchar(10),
borrow int,
repay int,
odate datetime
)

insert into #
select '9001', 10, 1, '2006-01-01' union all
select '9001', 12, 2, '2006-01-02' union all
select '9001', 13, 3, '2006-01-03' union all
select '9002', 18, 7, '2006-01-01' union all
select '9002', 19, 8, '2006-01-02' union all
select '9002', 20, 9, '2006-01-03'

select * from #

[子陌红尘]

create table test(sNo int,borrow int,repay int,odate datetime)
insert into test select 9001,10,1,'2006-01-01 00:00:00.000'
insert into test select 9001,12,2,'2006-01-02 00:00:00.000'
insert into test select 9001,13,3,'2006-01-03 00:00:00.000'
insert into test select 9002,18,7,'2006-01-01 00:00:00.000'
insert into test select 9002,19,8,'2006-01-02 00:00:00.000'
insert into test select 9002,20,9,'2006-01-03 00:00:00.000'

declare @sql1 varchar(8000),@sql2 varchar(8000)
set @sql1=''
set @sql2=''

select @sql1=@sql1+',['+odate+']=sum(case when datediff(dd,odate,'''+odate+''')=0 then borrow else 0 end)',
@sql2=@sql2+',['+odate+']=sum(case when datediff(dd,odate,'''+odate+''')=0 then repay else 0 end)'
from (select distinct convert(char(10),odate,120) as odate from test) t

set @sql1='select * from (select 1 as id,sNo'+@sql1+' from test group by sNo union select 2 as id,sNo'+@sql2+' from test group by sNo) t order by sNo,id'

exec(@sql1)
go

/*
id sNo 2006-01-01 2006-01-02 2006-01-03
----------- ----------- ----------- ----------- -----------
1 9001 10 12 13
2 9001 1 2 3
1 9002 18 19 20
2 9002 7 8 9
*/


drop table test
go

[子陌红尘]

create table test(sNo int,borrow int,repay int,odate datetime)
insert into test select 9001,10,1,'2006-01-01 00:00:00.000'
insert into test select 9001,12,2,'2006-01-02 00:00:00.000'
insert into test select 9001,13,3,'2006-01-03 00:00:00.000'
insert into test select 9002,18,7,'2006-01-01 00:00:00.000'
insert into test select 9002,19,8,'2006-01-02 00:00:00.000'
insert into test select 9002,20,9,'2006-01-03 00:00:00.000'

declare @sql1 varchar(8000),@sql2 varchar(8000),@sql3 varchar(8000)
set @sql1=''
set @sql2=''
set @sql3=''

select @sql1=@sql1+',['+odate+']=sum(case when datediff(dd,odate,'''+odate+''')=0 then borrow else 0 end)',
@sql2=@sql2+',['+odate+']=sum(case when datediff(dd,odate,'''+odate+''')=0 then repay else 0 end)',
@sql3=@sql3+',['+odate+']'
from (select distinct convert(char(10),odate,120) as odate from test) t

set @sql1='select sNo'+@sql3+' from (select 1 as id,sNo'+@sql1+' from test group by sNo union select 2 as id,sNo'+@sql2+' from test group by sNo) t order by sNo,id'

exec(@sql1)
go

/*
sNo 2006-01-01 2006-01-02 2006-01-03
----------- ----------- ----------- -----------
9001 10 12 13
9001 1 2 3
9002 18 19 20
9002 7 8 9
*/

drop table test
go

[paoluo]

create table #
(
sNo varchar(10),
borrow int,
repay int,
odate datetime
)

insert into #
select '9001', 10, 1, '2006-01-01' union all
select '9001', 12, 2, '2006-01-02' union all
select '9001', 13, 3, '2006-01-03' union all
select '9002', 18, 7, '2006-01-01' union all
select '9002', 19, 8, '2006-01-02' union all
select '9002', 20, 9, '2006-01-03'
GO
Declare @S1 Varchar(8000), @S2 Varchar(8000)
Select @S1 = ' Select sNo, 0 As Flag', @S2 = ' Select sNo, 1 As Flag'
Select @S1 = @S1 + ' , SUM(Case Convert(Varchar(10), odate, 120) When ''' + odate + ''' Then borrow Else 0 End) As [' + odate + ']',
@S2 = @S2 + ' , SUM(Case Convert(Varchar(10), odate, 120) When ''' + odate + ''' Then repay Else 0 End) As [' + odate + ']'
From
(Select Distinct Convert(Varchar(10), odate, 120) As odate From #) A Order By odate
EXEC(' Select * From (' + @S1 + ' From # Group By sNo Union All ' + @S2 + ' From # Group By sNo) A Order By sNo, Flag')
--select * from #

drop table #
--Result
/*
SNo Flag 2006-01-01 2006-01-02 2006-01-03
9001 0 10 12 13
9001 1 1 2 3
9002 0 18 19 20
9002 1 7 8 9
*/

[kadboy]

如果是2005,使用PIVOT/UNPIVOT

[paoluo]

想法差不多

[dawugui]

create table tb
(
sNo varchar(10),
borrow int,
repay int,
odate datetime
)

insert into tb
select '9001', 10, 1, '2006-01-01' union all
select '9001', 12, 2, '2006-01-02' union all
select '9001', 13, 3, '2006-01-03' union all
select '9002', 18, 7, '2006-01-01' union all
select '9002', 19, 8, '2006-01-02' union all
select '9002', 20, 9, '2006-01-03'
go

declare @sql1 varchar(8000)
set @sql1 = 'select sNo'
select @sql1 = @sql1 + ' , sum(case convert(varchar(10),odate,120) when ''' + convert(varchar(10),odate,120) + ''' then borrow else 0 end) [' + convert(varchar(10),odate,120) + ']'
from (select distinct convert(varchar(10),odate,120) odate from tb) as a
set @sql1 = @sql1 + ' from tb group by sNo'

set @sql1 = @sql1 + ' union all '

declare @sql2 varchar(8000)
set @sql2 = 'select sNo'
select @sql2 = @sql2 + ' , sum(case convert(varchar(10),odate,120) when ''' + convert(varchar(10),odate,120) + ''' then repay else 0 end) [' + convert(varchar(10),odate,120) + ']'
from (select distinct convert(varchar(10),odate,120) odate from tb) as a
set @sql2 = @sql2 + ' from tb group by sNo'

exec(@sql1 + @sql2)

drop table tb

/*
sNo 2006-01-01 2006-01-02 2006-01-03
---------- ----------- ----------- -----------
9001 10 12 13
9002 18 19 20
9001 1 2 3
9002 7 8 9

*/

[dawugui]

create table tb
(
sNo varchar(10),
borrow int,
repay int,
odate datetime
)

insert into tb
select '9001', 10, 1, '2006-01-01' union all
select '9001', 12, 2, '2006-01-02' union all
select '9001', 13, 3, '2006-01-03' union all
select '9002', 18, 7, '2006-01-01' union all
select '9002', 19, 8, '2006-01-02' union all
select '9002', 20, 9, '2006-01-03'
go

declare @sql1 varchar(8000)
set @sql1 = 'select * from ('
set @sql1 = @sql1 + 'select sNo'
select @sql1 = @sql1 + ' , sum(case convert(varchar(10),odate,120) when ''' + convert(varchar(10),odate,120) + ''' then borrow else 0 end) [' + convert(varchar(10),odate,120) + ']'
from (select distinct convert(varchar(10),odate,120) odate from tb) as a
set @sql1 = @sql1 + ' from tb group by sNo'

set @sql1 = @sql1 + ' union all '

declare @sql2 varchar(8000)
set @sql2 = 'select sNo'
select @sql2 = @sql2 + ' , sum(case convert(varchar(10),odate,120) when ''' + convert(varchar(10),odate,120) + ''' then repay else 0 end) [' + convert(varchar(10),odate,120) + ']'
from (select distinct convert(varchar(10),odate,120) odate from tb) as a
set @sql2 = @sql2 + ' from tb group by sNo) t order by sNo'

exec(@sql1 + @sql2)

drop table tb

/*
sNo 2006-01-01 2006-01-02 2006-01-03
---------- ----------- ----------- -----------
9001 1 2 3
9001 10 12 13
9002 7 8 9
9002 18 19 20
*/

[smile9961]

我的数据库用的是sql2000；所以不能用kadboy所说的方法。

非常感谢libin_ftsafe(子陌红尘：当libin告别ftsafe)、paoluo(一天到晚游泳的鱼) 、dawugui(潇洒老乌龟) 的精彩回复。虽然我也想到了在生产sql时加一个flag栏位，但是功力不够还是没能做出来。

[ojuju10]

declare @sql varchar(8000)
set @sql='select sno '
select @sql=@sql+', sum(case when convert( varchar(10),odate,120) = '''+odate+''' then borrow else 0 end) as ['+ odate+']'
from (select distinct convert(varchar(10), odate ,120) as odate from #)aa
select @sql=@sql+' from # group by sno '
set @sql=@sql+' union all '

declare @sql1 varchar(8000)
set @sql1='select sno '
select @sql1=@sql1+', sum(case when convert( varchar(10),odate,120) = '''+odate+''' then repay else 0 end) as ['+ odate+']'
from (select distinct convert(varchar(10), odate ,120) as odate from #)aa
select @sql1=@sql1+' from # group by sno '
set @sql1='select * from ( '+ @sql+@sql1+' ) as tt order by tt.sno '

exec(@sql1)