纵转横，新问题。
以下分别为编号是员工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
现在想将这些数据转为横向显示，即：
sNo2006-01-012006-01-022006-01-03
9001101213--9001 's borrow
9001123--9001 's repay
9002181920--9002 's borrow
9002789--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 #
(
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
/*
SNoFlag2006-01-012006-01-022006-01-03
90010101213
90011123
90020181920
90021789
*/
[解决办法]
如果是2005,使用PIVOT/UNPIVOT
[解决办法]
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
*/
