問題一
有一個表T2,它的內容如下
P1 MOUNT
a1 30
a1 40
a1 50
a1 60
如何用SQL把它變成下面的結果:
a1 30 40 50 60
--测试
Create table t2 (p1 char(10),mount int)
insert into t2 select 'a1',30
union all select 'a1',40
union all select 'a1',50
union all select 'a1',60
go
declare @s varchar(8000),@i int
select @s='',@i=max(aa) from(
select aa=count(*) from t2 group by p1
)awhile @i>0
select @s=',mount'+cast(@i as varchar)
+'=sum(case n when '+cast(@i as varchar)
+' then mount else 0 end)'+@s
,@i=@i-1
exec('select p1'+@s+' from(
select p1,mount,n=(
select sum(1) from t2
where p1=a.p1 and mount<=a.mount)
from t2 a
)a group by p1')
drop table t2
問題一
有一個表T2,它的內容如下
P1 MOUNT
a1 30
a1 40
a1 50
a1 60
如何用SQL把它變成下面的結果:
a1 30 40 50 60
--*/
declare @s varchar(8000)
set @s=''
select @s=@s+','+cast(MOUNT as varchar) from t2
exec('select ''a1'''+@s)
有一個表T1,它的內容如下
A1 A2 A3
1 2 3
4 5 6
7 8 9
如何用SQL把它的行列轉換,使它變成下面的結果:
A1 A2 A3
1 4 7
2 5 8
3 6 9
--*/
declare @s1 varchar(8000),@s2 varchar(8000),@s3 varchar(8000)
select @s1='',@s2='',@s3=''
select @s1=@s1+','+cast(A1 as varchar)
,@s2=@s2+','+cast(A2 as varchar)
,@s3=@s3+','+cast(A3 as varchar)
from t1
select @s1=stuff(@s1,1,1,'')
,@s3=stuff(@s2,1,1,'')
,@s3=stuff(@s3,1,1,'')
exec('select '+@s1+'
union all select '+@s2+'
union all select '+@s3)