竖表的数据如何转换到横表？

Question

declare @sql varchar(8000)
  set @sql = 'select xiangmu'
  select @sql = @sql +',['+ riqi +']=sum(case riqi when '''+ riqi +''' then shuju else 0 end)' from A group by riqi
  set @sql = @sql +' from A group by xiangmu'
  exec(@sql)

Answer 1

select xiangmu,
[1]=sum(case when substr(riqi,charindex('.',riqi)+1,len(riqi))=1 then shuju else 0 end),
[2]=sum(case when substr(riqi,charindex('.',riqi)+1,len(riqi))=2 then shuju else 0 end),
.....
[31]=sum(case when substr(riqi,charindex('.',riqi)+1,len(riqi))=31 then shuju else 0 end),
from 表a group by xiangmu

Answer 2

select xiangmu
       ,sum(case when right(riqi,charindex('.',riqi)-1)='1' 
                 then shuju else 0 end ) as '1'
       ,sum(case when right(riqi,charindex('.',riqi)-1)='2' 
                 then shuju else 0 end ) as '2'
       ,sum(case when right(riqi,charindex('.',riqi)-1)='3' 
                 then shuju else 0 end ) as '3'
       .........
       ,sum(case when right(riqi,charindex('.',riqi)-1)='30' 
                 then shuju else 0 end ) as '30'
       ,sum(case when right(riqi,charindex('.',riqi)-1)='31' 
                 then shuju else 0 end ) as '31'from A
group by xiangmu

Answer 3

[create] table a
(xiangmu varchar(20),riqi decimal(10,1),shuju int)insert a
select 'a',9.1,1000 [union] all
select 'b',9.1,800 [union] all
select 'a',9.2,500 [union] all
select 'b',9.2,300 [union] all
select 'a',9.3,5000declare @s varchar(2000),@i int
set @i = 30
set @s = ''
while @i >0
select @s=','+quotename(cast(@i as varchar))+'=isnull(sum(case when c='''+cast(@i as varchar)+''' then shuju end),0)'+@s,@i=@i-1
exec('select xiangmu'+@s+' from (
select c = stuff(cast(riqi as varchar),1,charindex(''.'',cast(riqi as varchar)),''''),xiangmu,shuju
from a
) a 
group by xiangmu')drop table a
 xiangmu              1           2           3           4           5           6           7           8           9           10          11          12          13          14          15          16          17          18          19          20          21          22          23          24          25          26          27          28          29          30          
-------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 
a                    1000        500         5000        0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0
b                    800         300         0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0我把create 和union 打上了[]

Answer 4

如果是想凭借riqi里面的小数位来排列的话，也就是说小数位有多少就排多少，那么程序就要改为
declare @s varchar(2000)
set @s = ''
select @s=@s+','+quotename(c)+'=isnull(sum(case when c='''+c+''' then shuju end),0)'
from
(select c=stuff(cast(riqi as varchar),1,charindex('.',cast(riqi as varchar)),'')
from a group by stuff(cast(riqi as varchar),1,charindex('.',cast(riqi as varchar)),'')
) a 
exec('select xiangmu'+@s+' from
(select xiangmu,c=stuff(cast(riqi as varchar),1,charindex(''.'',cast(riqi as varchar)),''''),shuju
 from a) a group by xiangmu')
drop table axiangmu              1           2           3           
-------------------- ----------- ----------- ----------- 
a                    1000        500         5000
b                    800         300         0