计算一行中所有列的特定文本 [英] Count specific text from all columns in a row
本文介绍了计算一行中所有列的特定文本的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
大家好,
我有这样的员工出勤表数据
ID NAME DAY1 DAY2 DAY3 DAY4 DAY5 DAY6 DAY7
------- ------ ---- ---- ---- ---- --- - ---- ----
empid001 jo 2 GG SL GGG
我需要输出像,
ID NAME DAY1 DAY2 DAY3 DAY4 DAY5 DAY6 DAY7 COUNTofG COUNTofSL COUNTof2
------- ------ ----- - ---- ---- ---- ---- ---- --------- --------- --------
empid001 jo 2 GG SL G SL G 4 2 1
我的结果集包含整个员工名单和每日出勤详情。<简单地说,我不知道如何从所有列中得到出勤类型(cl,sl等)的数量。
先谢谢,
Rajan。
解决方案
请看看例子:
DECLARE @ pvttable1 TABLE (ID VARCHAR ( 30 ),[NAME] < span class =code-keyword> VARCHAR ( 30 ),DAY1 VARCHAR (< span class =code-digit> 30 ),DAY2 VARCHAR ( 30 ), DAY3 VARCHAR ( 30 ),DAY4 VARCHAR ( 30 ),DAY5 VARCHAR ( 30 ),DAY6 VARCHAR ( 30 ),DAY7 VARCHAR ( 30 ))
INSERT INTO @ pvttable1 (ID,[NAME],DAY1,DAY2,DAY3,DAY4,DAY5,DAY6,DAY7)
VALUES (' empid001',' jo',' 2' ,' G',' G',' SL' ,' G',' G',' G'),
(' empid022',' oj',' G',' SL',< span class =code-string>' G',' SL',' G',' G',' G'),
(' empid333',' yo',' 2',' G',' SL',' SL',' G',' G',' G'),
(' empid999',' oy',' SL',' G',' G',' SL ',' G',' G',' SL')
DECLARE @ pvttable2 TABLE (ID VARCHAR ( 30 ),[名称] VARCHAR ( 30 ),[代码] < span class =code-keyword> VARCHAR ( 30 ),CountOfCode INT )
INSERT INTO @ pvttable2 (ID,[Name],[Code],CountOfCode)
SELECT ID,[Name],[Code],COUNT([Code] )CountOfCode
FROM (
SELECT ID,[NAME],[Day] ,[代码]
FROM (
SELECT ID,[NAME],DAY1 ,第2天,第3天,第4天,第5天,第6天,第7天
FROM @ pvttable1
) AS pvt
UNPIVOT([Code] FOR [Day] IN (DAY1,DAY2,DAY3,DAY4,DAY5,DAY6,DAY7)
) AS unpvt
) AS T
GROUP BY ID,[名称],[代码]
ORDER BY ID,[名称],[代码]
SELECT pvt1.ID,pvt1。 [名称],pvt1.DAY1,pvt1.DAY2,pvt1.DAY3,pvt1.DAY4,pvt1.DAY5,pvt1.DAY6,pvt1.DAY7, COALESCE (pvt2。 [ 2 ], 0 ) AS CountOf2 , COALESCE (pvt2.G, 0 ) AS CountOfG, COALESCE (pvt2.SL, 0 ) AS 计数OfSL
FROM @ pvttable1 AS pvt1 INNER JOIN (
SELECT ID,[Name],[ 2 ],[G],[SL]
FROM (
SELECT *
FROM @ pvttable2
)作为 DT
PIVOT(SUM(CountOfCode) FOR [代码] IN ([ 2 ],[G],[SL])) AS PVT
) AS pvt2 ON pvt1.ID = pvt2.ID
结果:
ID名称DAY1 DAY2 DAY3 DAY4 DAY5 DAY6 DAY7 Co..f2 Co..fG Co..fSL
empid001 jo 2 G G SL G G G 1 5 1
empid022 oj G SL G SL G G G 0 5 2
empid333 yo 2 G SL SL G G G 1 4 2
empid999 oy SL G G SL G G SL 0 4 3
你的表结构错误,需要将其标准化。
的EmpID 天 AttendanceCode empid001 第1天 2 empid001 第2天 ģ empid001 第三天 empid001 第四天 SL empid001 第五天 empid001 第六天 SL empid001 第七天 G
现在它很简单,可以计算出事件的数量。
然后为演示文稿制作一个透视图,当然属于表示层,而不是数据库即
Hi all,
I have an employee attendance table data like this
ID NAME DAY1 DAY2 DAY3 DAY4 DAY5 DAY6 DAY7
------- ------ ---- ---- ---- ---- ---- ---- ----
empid001 jo 2 G G SL G G G
I need the out put like,
ID NAME DAY1 DAY2 DAY3 DAY4 DAY5 DAY6 DAY7 COUNTofG COUNTofSL COUNTof2
------- ------ ---- ---- ---- ---- ---- ---- ---- --------- --------- --------
empid001 jo 2 G G SL G SL G 4 2 1
my result set having entire employee list with the daily attendance details.
simply, i don't know how to get the count of the attendance types(cl,sl,etc.,) from all columns.
Thanks in Advance,
Rajan.
解决方案
Please, have a look at example:
DECLARE @pvttable1 TABLE(ID VARCHAR(30), [NAME] VARCHAR(30), DAY1 VARCHAR(30), DAY2 VARCHAR(30), DAY3 VARCHAR(30), DAY4 VARCHAR(30), DAY5 VARCHAR(30), DAY6 VARCHAR(30), DAY7 VARCHAR(30)) INSERT INTO @pvttable1 (ID, [NAME], DAY1, DAY2, DAY3, DAY4, DAY5, DAY6, DAY7) VALUES('empid001', 'jo', '2', 'G', 'G', 'SL', 'G', 'G', 'G'), ('empid022', 'oj', 'G', 'SL', 'G', 'SL', 'G', 'G', 'G'), ('empid333', 'yo', '2', 'G', 'SL', 'SL', 'G', 'G', 'G'), ('empid999', 'oy', 'SL', 'G', 'G', 'SL', 'G', 'G', 'SL') DECLARE @pvttable2 TABLE (ID VARCHAR(30), [Name] VARCHAR(30), [Code] VARCHAR(30), CountOfCode INT) INSERT INTO @pvttable2 (ID, [Name], [Code], CountOfCode) SELECT ID, [Name], [Code], COUNT([Code]) CountOfCode FROM ( SELECT ID, [NAME], [Day], [Code] FROM( SELECT ID, [NAME], DAY1, DAY2, DAY3, DAY4, DAY5, DAY6, DAY7 FROM @pvttable1 ) AS pvt UNPIVOT ([Code] FOR [Day] IN (DAY1, DAY2, DAY3, DAY4, DAY5, DAY6, DAY7) ) AS unpvt ) AS T GROUP BY ID, [Name], [Code] ORDER BY ID, [Name], [Code] SELECT pvt1.ID, pvt1.[Name], pvt1.DAY1, pvt1.DAY2, pvt1.DAY3, pvt1.DAY4, pvt1.DAY5, pvt1.DAY6, pvt1.DAY7, COALESCE(pvt2.[2],0) AS CountOf2, COALESCE(pvt2.G,0) AS CountOfG, COALESCE(pvt2.SL,0) AS CountOfSL FROM @pvttable1 AS pvt1 INNER JOIN ( SELECT ID, [Name], [2], [G], [SL] FROM ( SELECT * FROM @pvttable2 ) As DT PIVOT(SUM(CountOfCode) FOR [Code] IN ([2], [G], [SL])) AS PVT ) AS pvt2 ON pvt1.ID = pvt2.ID
Result:
ID Name DAY1 DAY2 DAY3 DAY4 DAY5 DAY6 DAY7 Co..f2 Co..fG Co..fSL empid001 jo 2 G G SL G G G 1 5 1 empid022 oj G SL G SL G G G 0 5 2 empid333 yo 2 G SL SL G G G 1 4 2 empid999 oy SL G G SL G G SL 0 4 3
Your table structure is wrong, you need to normalize it.
EmpID Day AttendanceCode empid001 Day1 2 empid001 Day2 G empid001 Day3 G empid001 Day4 SL empid001 Day5 G empid001 Day6 SL empid001 Day7 G
Now it's simple enough to make a count on the occurencies.
Then make a pivot for the presentation, which of course belongs to the presentation layer, not the database.
这篇关于计算一行中所有列的特定文本的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文