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
------解决方案--------------------
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
*/
------解决方案--------------------
结果又错，稍改了下。。
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),