请教含字母与数字的多列数据求和

Question

CREATE TABLE ats_test
  (
     empcode      NVARCHAR(40),
     Day_01       NVARCHAR(10),
     Day_02       NVARCHAR(10),
     Day_03       NVARCHAR(10),
     Day_04       NVARCHAR(10),
     Day_05       NVARCHAR(10),
     Day_06       NVARCHAR(10),
     Day_07       NVARCHAR(10),
     Day_08       NVARCHAR(10),
     Day_09       NVARCHAR(10),
     Day_10       NVARCHAR(10)
  ) insert into ats_test values ('EAA45463','N4/D2/S2','N4/D2/S2','L7.5','N7.5','N7.5','N7.5','N7.5','N7.5','N7.5','N7.5')
insert into ats_test values ('EAA81137','X','L8','L8','N7.5','N7.5','N7.5','X','X','X','N6.5')
求高手们指导下，如何统计Day_01~Day_10列中第一个字母相同的数据的数字部分和？
其中比较特殊的是一个字段数据可以是'N4/D2/S2'
我想做个视图，对每个人做统计，统计信息的列名就为empcode、N、D、S、X、L
sql

Answer 1

CREATE TABLE ats_test
(
 empcode      NVARCHAR(40),
 Day_01       NVARCHAR(10),
 Day_02       NVARCHAR(10),
 Day_03       NVARCHAR(10),
 Day_04       NVARCHAR(10),
 Day_05       NVARCHAR(10),
 Day_06       NVARCHAR(10),
 Day_07       NVARCHAR(10),
 Day_08       NVARCHAR(10),
 Day_09       NVARCHAR(10),
 Day_10       NVARCHAR(10)
) 
insert into ats_test values ('EAA45463','N4/D2/S2','N4/D2/S2','L7.5','N7.5','N7.5','N7.5','N7.5','N7.5','N7.5','N7.5')
insert into ats_test values ('EAA81137','X','L8','L8','N7.5','N7.5','N7.5','X','X','X','N6.5')
go 
;with cte as 
(
select empcode,Day_01 as [key] from ats_test
union all select empcode,Day_02 from ats_test
union all select empcode,Day_03 from ats_test
union all select empcode,Day_04 from ats_test
union all select empcode,Day_05 from ats_test
union all select empcode,Day_06 from ats_test
union all select empcode,Day_07 from ats_test
union all select empcode,Day_08 from ats_test
union all select empcode,Day_09 from ats_test
union all select empcode,Day_10 from ats_test
),
cte2 as
(
select empcode,left([key],1) as [key],STUFF([key],1,1,'') as num
from (
SELECT empcode, SUBSTRING([key],number,CHARINDEX('/',[key]+'/',number)-number) as [key]
from cte a, master..spt_values 
where [key] like '%/%' and number >=1 and type='p' 
and number<len([key])  and substring('/'+[key],number,1)='/'
union all
select empcode,[key]
from cte
where [key] not like '%/%' 
)t
)select *
from 
(select empcode, [key] as [key],sum(cast(num as numeric(10,2))) as num    
from cte2 where ISNUMERIC(num)=1
group by empcode,[key]
) as x
pivot (sum(num)for [key] in (D,L,N,S,X)) as pvt
order by empcode/*
empcode D L N S X
---------------------------------------------------------
EAA45463 4.00 7.50 60.50 4.00 NULL
EAA81137 NULL 16.00 29.00 NULL NULL
*/

Answer 2

笨办法写了个
IF OBJECT_ID('tempdb..#ats_test','U') IS NOT NULL DROP TABLE #ats_test
CREATE TABLE #ats_test
  (
     empcode      NVARCHAR(40),
     Day_01       NVARCHAR(10),
     Day_02       NVARCHAR(10),
     Day_03       NVARCHAR(10),
     Day_04       NVARCHAR(10),
     Day_05       NVARCHAR(10),
     Day_06       NVARCHAR(10),
     Day_07       NVARCHAR(10),
     Day_08       NVARCHAR(10),
     Day_09       NVARCHAR(10),
     Day_10       NVARCHAR(10)
  ) insert into #ats_test values ('EAA45463','N4/D2/S2','N4/D2/S2','L7.5','N7.5','N7.5','N7.5','N7.5','N7.5','N7.5','N7.5')
insert into #ats_test values ('EAA81137','X','L8','L8','N7.5','N7.5','N7.5','X','X','X','N6.5')IF OBJECT_ID('tempdb..#TempA','U') IS NOT NULL DROP TABLE #TempA
CREATE TABLE #TempA(
Id INT IDENTITY(1,1)
,empcode  NVARCHAR(40)
,Days NVARCHAR(6)
,Num NVARCHAR(10)
,ColN NVARCHAR(10)
,ColD NVARCHAR(10)
,ColS NVARCHAR(10)
,ColX NVARCHAR(10)
,ColL NVARCHAR(10)
)
INSERT INTO #TempA
SELECT empcode,DS AS Days,Num,0,0,0,0,0
FROM #ats_test P
UNPIVOT
(
Num FOR DS IN (Day_01,Day_02,Day_03,Day_04,Day_05,Day_06,Day_07,Day_08,Day_09,Day_10)
)AS uppvtDECLARE @Cnt INT=(SELECT Max(Id) FROM #TempA)
DECLARE @Txt NVARCHAR(10)='',@Num NVARCHAR(10)=''
DECLARE @Index INTWHILE @Cnt>0
BEGIN
SELECT @Txt=Num FROM #TempA WHERE Id=@Cnt
WHILE LEN(@Txt)>0
BEGIN
IF CHARINDEX('/',@Txt)>0
BEGIN
SET @Index=CHARINDEX('/',@Txt)
SET @Num=LEFT(@Txt,@Index-1)
IF CHARINDEX('N',@Num)>0
UPDATE #TempA SET ColN=CASE WHEN STUFF(@Num,1,1,'')='' THEN '0' ELSE STUFF(@Num,1,1,'') END  WHERE Id=1
IF CHARINDEX('D',@Num)>0
UPDATE #TempA SET ColD=CASE WHEN STUFF(@Num,1,1,'')='' THEN '0' ELSE STUFF(@Num,1,1,'') END  WHERE Id=1
IF CHARINDEX('S',@Num)>0
UPDATE #TempA SET ColS=CASE WHEN STUFF(@Num,1,1,'')='' THEN '0' ELSE STUFF(@Num,1,1,'') END  WHERE Id=1
IF CHARINDEX('X',@Num)>0
UPDATE #TempA SET ColX=CASE WHEN STUFF(@Num,1,1,'')='' THEN '0' ELSE STUFF(@Num,1,1,'') END  WHERE Id=1
IF CHARINDEX('L',@Num)>0
UPDATE #TempA SET ColL=CASE WHEN STUFF(@Num,1,1,'')='' THEN '0' ELSE STUFF(@Num,1,1,'') END  WHERE Id=1

SET @Txt=STUFF(@Txt,1,CHARINDEX('/',@Txt),'')

END ELSE 
BEGIN
IF CHARINDEX('N',@Txt)>0
UPDATE #TempA SET ColN=CASE WHEN STUFF(@Txt,1,1,'')='' THEN '0' ELSE STUFF(@Txt,1,1,'') END WHERE Id=@Cnt
IF CHARINDEX('D',@Txt)>0
UPDATE #TempA SET ColD=CASE WHEN STUFF(@Txt,1,1,'')='' THEN '0' ELSE STUFF(@Txt,1,1,'') END WHERE Id=@Cnt
IF CHARINDEX('S',@Txt)>0
UPDATE #TempA SET ColS=CASE WHEN STUFF(@Txt,1,1,'')='' THEN '0' ELSE STUFF(@Txt,1,1,'') END WHERE Id=@Cnt
IF CHARINDEX('X',@Txt)>0
UPDATE #TempA SET ColX=CASE WHEN STUFF(@Txt,1,1,'')='' THEN '0' ELSE STUFF(@Txt,1,1,'') END WHERE Id=@Cnt
IF CHARINDEX('L',@Txt)>0
UPDATE #TempA SET ColL=CASE WHEN STUFF(@Txt,1,1,'')='' THEN '0' ELSE STUFF(@Txt,1,1,'') END WHERE Id=@Cnt

SET @Txt=STUFF(@Txt,1,LEN(@Txt),'')

END
END
SET @Cnt=@Cnt-1
ENDSElECT empcode
,SUM(CAST(ColN AS DECIMAL(9,2))) AS ColN
,SUM(CAST(ColD AS DECIMAL(9,2))) AS ColD
,SUM(CAST(ColS AS DECIMAL(9,2))) AS ColS
,SUM(CAST(ColX AS DECIMAL(9,2))) AS ColX
,SUM(CAST(ColL AS DECIMAL(9,2))) AS ColL
FROM #TempA
GROUP BY empcode
/*
empcode ColN ColD ColS ColX ColL
EAA45463 56.50 2.00 4.00 0.00 7.50
EAA81137 29.00 0.00 0.00 0.00 16.00
*/

Answer 3

结果又错，稍改了下。。
IF OBJECT_ID('tempdb..#ats_test','U') IS NOT NULL DROP TABLE #ats_test
CREATE TABLE #ats_test
  (
     empcode      NVARCHAR(40),
     Day_01       NVARCHAR(10),
     Day_02       NVARCHAR(10),
     Day_03       NVARCHAR(10),
     Day_04       NVARCHAR(10),
     Day_05       NVARCHAR(10),
     Day_06       NVARCHAR(10),
     Day_07       NVARCHAR(10),
     Day_08       NVARCHAR(10),
     Day_09       NVARCHAR(10),
     Day_10       NVARCHAR(10)
  ) insert into #ats_test values ('EAA45463','N4/D2/S2','N4/D2/S2','L7.5','N7.5','N7.5','N7.5','N7.5','N7.5','N7.5','N7.5')
insert into #ats_test values ('EAA81137','X','L8','L8','N7.5','N7.5','N7.5','X','X','X','N6.5')IF OBJECT_ID('tempdb..#TempA','U') IS NOT NULL DROP TABLE #TempA
CREATE TABLE #TempA(
Id INT IDENTITY(1,1)
,empcode  NVARCHAR(40)
,Days NVARCHAR(6)
,Num NVARCHAR(10)
,ColN NVARCHAR(10)
,ColD NVARCHAR(10)
,ColS NVARCHAR(10)
,ColX NVARCHAR(10)
,ColL NVARCHAR(10)
)
INSERT INTO #TempA
SELECT empcode,DS AS Days,Num,0,0,0,0,0
FROM #ats_test P
UNPIVOT
(
Num FOR DS IN (Day_01,Day_02,Day_03,Day_04,Day_05,Day_06,Day_07,Day_08,Day_09,Day_10)
)AS uppvtDECLARE @Cnt INT=(SELECT Max(Id) FROM #TempA)
DECLARE @Txt NVARCHAR(10)='',@Num NVARCHAR(10)=''
DECLARE @Index INTWHILE @Cnt>0
BEGIN
SELECT @Txt=Num FROM #TempA WHERE Id=@Cnt
WHILE LEN(@Txt)>0
BEGIN
IF CHARINDEX('/',@Txt)>0
BEGIN
SET @Index=CHARINDEX('/',@Txt)
SET @Num=LEFT(@Txt,@Index-1)
IF CHARINDEX('N',@Num)>0
UPDATE #TempA SET ColN=CASE WHEN STUFF(@Num,1,1,'')='' THEN '0' ELSE STUFF(@Num,1,1,'') END  WHERE Id=@Cnt
IF CHARINDEX('D',@Num)>0
UPDATE #TempA SET ColD=CASE WHEN STUFF(@Num,1,1,'')='' THEN '0' ELSE STUFF(@Num,1,1,'') END  WHERE Id=@Cnt
IF CHARINDEX('S',@Num)>0
UPDATE #TempA SET ColS=CASE WHEN STUFF(@Num,1,1,'')='' THEN '0' ELSE STUFF(@Num,1,1,'') END  WHERE Id=@Cnt
IF CHARINDEX('X',@Num)>0
UPDATE #TempA SET ColX=CASE WHEN STUFF(@Num,1,1,'')='' THEN '0' ELSE STUFF(@Num,1,1,'') END  WHERE Id=@Cnt
IF CHARINDEX('L',@Num)>0
UPDATE #TempA SET ColL=CASE WHEN STUFF(@Num,1,1,'')='' THEN '0' ELSE STUFF(@Num,1,1,'') END  WHERE Id=@Cnt

SET @Txt=STUFF(@Txt,1,CHARINDEX('/',@Txt),'')

END ELSE 
BEGIN
IF CHARINDEX('N',@Txt)>0
UPDATE #TempA SET ColN=CASE WHEN STUFF(@Txt,1,1,'')='' THEN '0' ELSE STUFF(@Txt,1,1,'') END WHERE Id=@Cnt
IF CHARINDEX('D',@Txt)>0
UPDATE #TempA SET ColD=CASE WHEN STUFF(@Txt,1,1,'')='' THEN '0' ELSE STUFF(@Txt,1,1,'') END WHERE Id=@Cnt
IF CHARINDEX('S',@Txt)>0
UPDATE #TempA SET ColS=CASE WHEN STUFF(@Txt,1,1,'')='' THEN '0' ELSE STUFF(@Txt,1,1,'') END WHERE Id=@Cnt
IF CHARINDEX('X',@Txt)>0
UPDATE #TempA SET ColX=CASE WHEN STUFF(@Txt,1,1,'')='' THEN '0' ELSE STUFF(@Txt,1,1,'') END WHERE Id=@Cnt
IF CHARINDEX('L',@Txt)>0
UPDATE #TempA SET ColL=CASE WHEN STUFF(@Txt,1,1,'')='' THEN '0' ELSE STUFF(@Txt,1,1,'') END WHERE Id=@Cnt

SET @Txt=STUFF(@Txt,1,LEN(@Txt),'')

END
END
SET @Cnt=@Cnt-1
ENDSElECT empcode
,SUM(CAST(ColN AS DECIMAL(9,2))) AS ColN
,SUM(CAST(ColD AS DECIMAL(9,2))) AS ColD
,SUM(CAST(ColS AS DECIMAL(9,2))) AS ColS
,SUM(CAST(ColX AS DECIMAL(9,2))) AS ColX
,SUM(CAST(ColL AS DECIMAL(9,2))) AS ColL
FROM #TempA
GROUP BY empcode/*
empcode ColN ColD ColS ColX ColL
EAA45463 60.50 4.00 4.00 0.00 7.50
EAA81137 29.00 0.00 0.00 0.00 16.00
*/

Answer 4

这个主要是通过先建个函数，然后再进行分拆，可以指定按照哪个字符进行分拆，最后计算：
--建一个字符串拆分函数
if exists(select * from sys.objects where name = 'f_splitSTR' and type = 'tf')
   drop function dbo.f_splitSTR
gocreate function dbo.f_splitSTR
(
@s varchar(8000),     --要分拆的字符串
@split varchar(10)    --分隔字符
) 
returns @re table(                      --要返回的临时表
                     col varchar(1000)  --临时表中的列 
                 )
as
begin   
  declare @len int
  
  set @len = LEN(@split)      --分隔符不一定就是一个字符，可能是2个字符
  
  while CHARINDEX(@split,@s) >0
  begin
insert into @re 
values(left(@s,charindex(@split,@s) - 1))

set @s = STUFF(@s,1,charindex(@split,@s) - 1 + @len ,'')    --覆盖：字符串以及分隔符
  end
  
  insert into @re values(@s)
  
  return   --返回临时表
end
go  
If object_id('ats_test') is not null
   drop table ats_test
go--建表
CREATE TABLE ats_test
(empcode      NVARCHAR(40),
 Day_01       NVARCHAR(10),
 Day_02       NVARCHAR(10),
 Day_03       NVARCHAR(10),
 Day_04       NVARCHAR(10),
 Day_05       NVARCHAR(10),
 Day_06       NVARCHAR(10),
 Day_07       NVARCHAR(10),
 Day_08       NVARCHAR(10),
 Day_09       NVARCHAR(10),
 Day_10       NVARCHAR(10))
  
insert into ats_test values ('EAA45463','N4/D2/S2','N4/D2/S2','L7.5','N7.5','N7.5','N7.5','N7.5','N7.5','N7.5','N7.5')
insert into ats_test values ('EAA81137','X','L8','L8','N7.5','N7.5','N7.5','X','X','X','N6.5')
go
;with t
as
(
select empcode,day_01,col from ats_test a cross apply dbo.f_splitSTR(a.day_01,'/') union all
select empcode,day_02,col from ats_test a cross apply dbo.f_splitSTR(a.day_02,'/') union all
select empcode,day_03,col from ats_test a cross apply dbo.f_splitSTR(a.day_03,'/') union all
select empcode,day_04,col from ats_test a cross apply dbo.f_splitSTR(a.day_04,'/') union all
select empcode,day_05,col from ats_test a cross apply dbo.f_splitSTR(a.day_05,'/') union all
select empcode,day_06,col from ats_test a cross apply dbo.f_splitSTR(a.day_06,'/') union all
select empcode,day_07,col from ats_test a cross apply dbo.f_splitSTR(a.day_07,'/') union all
select empcode,day_08,col from ats_test a cross apply dbo.f_splitSTR(a.day_08,'/') union all
select empcode,day_09,col from ats_test a cross apply dbo.f_splitSTR(a.day_09,'/') union all
select empcode,day_10,col from ats_test a cross apply dbo.f_splitSTR(a.day_10,'/') 
)select empcode,
       sum(case when left(col,1) = 'D' then cal else 0.0 end) as D,
       sum(case when left(col,1) = 'L' then cal else 0.0 end) as L,
       sum(case when left(col,1) = 'N' then cal else 0.0 end) as N, 
       sum(case when left(col,1) = 'S' then cal else 0.0 end) as S,
       sum(case when left(col,1) = 'X' then cal else 0.0 end) as X                                          from 
(
select *,case when len(col) > 1 
                   then cast(substring(col,2,len(col)-1) as numeric(20,1))
               else 0
         end as cal
from t
)t
group by empcode
/*
empcode     D L  N   S     X
EAA45463 4.0 7.5  60.5 4.0 0.0
EAA81137 0.0 16.0   29.0 0.0 0.0
*/