sql行列转换,字符串相加

if object_id('tb')is not null

drop table tb;

CREATE TABLE [dbo].[tb](
    [name] [varchar](50) NULL,
    [class] [varchar](50) NULL,
    [score] [int] NULL
) ON [PRIMARY]

insert into tb values('张三','语文',74)
insert into tb values('张三','数学',83)
insert into tb values('张三','物理',93)
insert into tb values('李四','语文',74)
insert into tb values('李四','数学',84)
insert into tb values('李四','物理',94)

使用case ..when..then..else..end

1.静态的

select name ,MAX(case class when N'语文' then score else 0 end) 语文,
MAX(case class when N'数学' then score else 0 end) 数学,
MAX(case class when N'物理' then score else 0 end) 物理,
sum(score) 总分,
cast(avg(score*1.0)as decimal(18,2))平均分
from tb group by name

动态的

declare @sql varchar(8000)
set @sql = 'select name '
select @sql = @sql + ' , max(case class when ''' + class + ''' then score else 0 end) [' + class + ']'//循环调用
from (select distinct class from tb) as a
set @sql = @sql + ' from tb group by name'
print(@sql);
exec(@sql)

使用pivot

静态

select m.*,n.总分,n.平均分
from
(select * from tb pivot(max(score)for class in(语文,数学,物理))a)m,
(select name,sum(score)总分,cast(avg(score*1.0)as decimal(18,2))平均分
from tb
group by name)n
where m.name=n.name

动态

declare @sql varchar(8000)
set @sql=''  --初始化变量@sql
select @sql=@sql+','+class from tb group by class--变量多值赋值
--同select @sql = @sql + ','+课程from (select distinct课程from tb)a
set @sql=stuff(@sql,1,1,'')--去掉首个','
set @sql='select m.* , n.总分,n.平均分 from
(select * from (select * from tb) a pivot (max(score) for class in ('+@sql+')) b) m ,
(select name,sum(score)总分, cast(avg(score*1.0) as decimal(18,2))平均分 from tb group by name) n
where m.name= n.name'
print(@sql);
exec(@sql)

 stuff用法：

以下示例在第一个字符串 abcdef 中删除从第 2 个位置（字符 b）开始的三个字符，然后在删除的起始位置插入第二个字符串，从而创建并返回一个字符串

SELECT STUFF('abcdef', 2, 3, 'ijklmn')，有点想js中的splice

例子：

有个表Kqjl_Info

有三个字段，sfzh(身份证号，进出时间，进出状态（1进，0出））

计算一个员工是否上班，就看他的进出状态是否有1,0，就是先进，然后再出

最后想输出

就是一个月内这个人上了那几天班

具体sql:

with ta as (select sfzh,kqsj,jczt from Kqjl_Info)
select sfzh,
max(case dd when 1 then jczt else 0 end) d1,
max(case dd when 2 then jczt else 0 end) d2,
max(case dd when 3 then jczt else 0 end) d3,
max(case dd when 4 then jczt else 0 end) d4,
max(case dd when 5 then jczt else 0 end) d5,
max(case dd when 6 then jczt else 0 end) d6,
max(case dd when 7 then jczt else 0 end) d7,
max(case dd when 8 then jczt else 0 end) d8,
max(case dd when 9 then jczt else 0 end) d9,
max(case dd when 10 then jczt else 0 end) d10,
max(case dd when 11 then jczt else 0 end) d11,
max(case dd when 12 then jczt else 0 end) d12,
max(case dd when 13 then jczt else 0 end) d13,
max(case dd when 14 then jczt else 0 end) d14,
max(case dd when 15 then jczt else 0 end) d15,
max(case dd when 16 then jczt else 0 end) d16,
max(case dd when 17 then jczt else 0 end) d17,
max(case dd when 18 then jczt else 0 end) d18,
max(case dd when 19 then jczt else 0 end) d19,
max(case dd when 20 then jczt else 0 end) d20,
max(case dd when 21 then jczt else 0 end) d21,
max(case dd when 22 then jczt else 0 end) d22,
max(case dd when 23 then jczt else 0 end) d23,
max(case dd when 24 then jczt else 0 end) d24,
max(case dd when 25 then jczt else 0 end) d25,
max(case dd when 26 then jczt else 0 end) d26,
max(case dd when 27 then jczt else 0 end) d27,
max(case dd when 28 then jczt else 0 end) d28,
max(case dd when 29 then jczt else 0 end) d29,
max(case dd when 30 then jczt else 0 end) d30,
max(case dd when 31 then jczt else 0 end) d31,
sum(jczt) totalCount
from (select sfzh,dd,jczt=1 from (select sfzh,day(kqsj) as dd,
(select cast(jczt as varchar)+',' from ta where sfzh=a.sfzh and day(kqsj)=day(a.kqsj) 
order by kqsj  for xml path('') ) as zt from ta a group by sfzh,day(kqsj)) as tb 
where tb.zt like '%1,0%') as tc group by sfzh;

分析：

先

with ta as (select sfzh,kqsj,jczt from Kqjl_Info)
 select sfzh,day(kqsj),(select cast(jczt as varchar)+',' from ta where sfzh=a.sfzh and day(kqsj)=day(a.kqsj) order by kqsj  for xml path('') ) as zt from ta a group by sfzh,day(kqsj)

再选择有1,0的员工，再行列转换

 

use EntranceVista;
  with ta as ( select a.sfzh,a.kqsj,a.jczt,b.xm,gz=b.gz1,a.qybmm  from Kqjl_Info a join Ry_Info b on a.sfzh=b.sfzh  where  1=1  and a.kqsj<='2015-08-25 23:59:59' and a.kqsj>='2015-07-26 00:00:00' )
 select xh=ROW_NUMBER()over (order  by qybmm,xm ),tt.*,b.xm,b.gz,b.qybmm from ta b join(select aa='',bb='',cc='',dd='',ee='',ff='',gg='',hh='',ii='',jj='',sfzh,totalCount=COUNT(1) from ( select sfzh,day(kqsj) as dd,(select cast(jczt as varchar)+',' from ta where sfzh=a.sfzh and day(kqsj)=day(a.kqsj) order by kqsj  for xml path('') ) as zt from ta a group by sfzh,day(kqsj)) as td where zt like '%1,0%' group by sfzh ) tt  on tt.sfzh=b.sfzh order by b.qybmm,b.xm



select xh=ROW_NUMBER()over (order  by qybmm,xm ),tt.*,b.xm,b.gz,b.qybmm from  ta b join( select sfzh, 
 max(case dd when 1 then jczt else 0 end) d1,
 max(case dd when 2 then jczt else 0 end) d2,
 max(case dd when 3 then jczt else 0 end) d3,
 max(case dd when 4 then jczt else 0 end) d4,max(case dd when 5 then jczt else 0 end) d5,max(case dd when 6 then jczt else 0 end) d6,max(case dd when 7 then jczt else 0 end) d7,max(case dd when 8 then jczt else 0 end) d8,max(case dd when 9 then jczt else 0 end) d9,max(case dd when 10 then jczt else 0 end) d10,max(case dd when 11 then jczt else 0 end) d11,max(case dd when 12 then jczt else 0 end) d12,max(case dd when 13 then jczt else 0 end) d13,max(case dd when 14 then jczt else 0 end) d14,max(case dd when 15 then jczt else 0 end) d15,max(case dd when 16 then jczt else 0 end) d16,max(case dd when 17 then jczt else 0 end) d17,max(case dd when 18 then jczt else 0 end) d18,max(case dd when 19 then jczt else 0 end) d19,max(case dd when 20 then jczt else 0 end) d20,max(case dd when 21 then jczt else 0 end) d21,max(case dd when 22 then jczt else 0 end) d22,max(case dd when 23 then jczt else 0 end) d23,max(case dd when 24 then jczt else 0 end) d24,max(case dd when 25 then jczt else 0 end) d25,max(case dd when 26 then jczt else 0 end) d26,max(case dd when 27 then jczt else 0 end) d27,max(case dd when 28 then jczt else 0 end) d28,max(case dd when 29 then jczt else 0 end) d29,max(case dd when 30 then jczt else 0 end) d30,
 max(case dd when 31 then jczt else 0 end) d31,
 sum(jczt) totalCount  from (
 select sfzh,dd,jczt=1 from (
 select sfzh,day(kqsj) as dd, (
 select cast(jczt as varchar)+',' from ta where sfzh=a.sfzh and day(kqsj)=day(a.kqsj)  order by kqsj  for xml path('') ) as zt 
 from ta a group by sfzh,day(kqsj)) as tb 
  where tb.zt like '%1,0%') as tc group by sfzh ) tt   on tt.sfzh=b.sfzh order by b.qybmm,b.xm

 

DBCC DROPCLEANBUFFERS
  --清除buffer pool里的所有缓存数据
 DBCC freeproccache
 GO

  --清除buffer pool里的所有缓存的执行计划
  SET STATISTICS TIME ON
  GO
  USE [EntranceVista]
 GO
  with ta as (select sfzh,kqsj,jczt from Kqjl_Info)
 select sfzh,day(kqsj),(select cast(jczt as varchar)+',' from ta where sfzh=a.sfzh and day(kqsj)=day(a.kqsj) order by kqsj  for xml path('') ) as zt from ta a group by sfzh,day(kqsj) 

 GO
 SET STATISTICS TIME OFF
 GO

报告：

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

 SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 39 毫秒。

(47390 行受影响)

 SQL Server 执行时间:
   CPU 时间 = 15802 毫秒，占用时间 = 18523 毫秒。