Imported Excel sheet To SQL server.i have selects one table . in this table have lot of Rows values. in this Rows single Employee comes in this Table many time. i want single Employee in this COlumn should have all values available from Repeated columns. i will show Sample Data for reference.
导入的Excel工作表到SQL server.i已选择一个表。在此表中有很多行值。在这个行中,单个员工多次来到这个表。我希望此COlumn中的单个Employee应该具有重复列中的所有值。我将展示样本数据以供参考。
Name E1 E2 E3 E4 E5
Jeni 1 0 0 0 0
Jeni 0 0 2 0 0
Jeni 0 5 0 0 3
Priya 0 3 0 0 0
Priya 0 0 0 0 3
Priya 0 0 7 0 0
Priya 10 0 0 0 0
My table Looks like after Select the Table my Result should be Like
我的表看起来像选择表我的结果应该是喜欢
Name E1 E2 E3 E4 E5 Total
jeni 1 5 2 0 3 11
Priya 10 3 7 0 3 23
i want be like this. i hope Explained little bit clear.please how can achieve this. please also Refer BEST TUTORIAL For learn SQL very short period .thank you advance . I hope some Wil help. I am new SQL Server.
我希望是这样的。我希望解释得有点清楚。请问如何实现这一点。请参考BEST TUTORIAL学习SQL很短的时间。谢谢你的进步。我希望Wil有所帮助。我是新的SQL Server。
3 个解决方案
#1
5
CREATE TABLE #Table1
([Name] varchar(5), [E1] int, [E2] int, [E3] int, [E4] int, [E5] int)
;
INSERT INTO #Table1
([Name], [E1], [E2], [E3], [E4], [E5])
VALUES
('Jeni', 1, 0, 0, 0, 0),
('Jeni', 0, 0, 2, 0, 0),
('Jeni', 0, 5, 0, 0, 3),
('Priya', 0, 3, 0, 0, 0),
('Priya', 0, 0, 0, 0, 3),
('Priya', 0, 0, 7, 0, 0),
('Priya', 10, 0, 0, 0, 0)
;
SELECT *,A.E1+A.E2+A.E3+A.E4+A.E5 AS 'TOTAL' FROM ( SELECT NAME, SUM(E1) E1 ,SUM(E2) E2 ,SUM(E3) E3 ,SUM(E4) E4 ,SUM(E5) E5 FROM #TABLE1 GROUP BY NAME)A
output
NAME E1 E2 E3 E4 E5 TOTAL
Jeni 1 5 2 0 3 11
Priya 10 3 7 0 3 23
#2
3
The thing you are looking for is a Group by .
你要找的东西是Group by。
To get the result you are looking for , i just made the table you described and did a group by and did a sum on the integer values.
为了获得您正在寻找的结果,我只是制作了您描述的表并进行了分组,并对整数值进行了总结。
select name, sum(E1),sum(E2),sum(E3),sum(E4),sum(E5) from Test Group by name
#3
0
;With cte(Name,E1,E2,E3,E4,E5)
AS
(
SELECT 'Jeni' ,1 ,0 , 0, 0, 0 Union all
SELECT 'Jeni' ,0 ,0 , 2, 0, 0 Union all
SELECT 'Jeni' ,0 ,5 , 0, 0, 3 Union all
SELECT 'Priya',0 ,3 , 0, 0, 0 Union all
SELECT 'Priya',0 ,0 , 0, 0, 3 Union all
SELECT 'Priya',0 ,0 , 7, 0, 0 Union all
SELECT 'Priya',10,0 , 0, 0, 0
)
SELECT Name,E1,E2,E3,E4,E5,Total FROM
(
SELECT *,Row_number()Over(Partition by Name order by Name)AS Seq From
(
SELECT Name,SUM(E1)E1,
SUM(E2)E2,
SUM(E3)E3,
SUM(E4)E4,
SUM(E5)E5,
SUM(E1+E2+ E3+ E4+E5) AS Total
FROM cte
Group by Name
)Dt
)AS Final
WHERE Final.Seq=1
#1
5
CREATE TABLE #Table1
([Name] varchar(5), [E1] int, [E2] int, [E3] int, [E4] int, [E5] int)
;
INSERT INTO #Table1
([Name], [E1], [E2], [E3], [E4], [E5])
VALUES
('Jeni', 1, 0, 0, 0, 0),
('Jeni', 0, 0, 2, 0, 0),
('Jeni', 0, 5, 0, 0, 3),
('Priya', 0, 3, 0, 0, 0),
('Priya', 0, 0, 0, 0, 3),
('Priya', 0, 0, 7, 0, 0),
('Priya', 10, 0, 0, 0, 0)
;
SELECT *,A.E1+A.E2+A.E3+A.E4+A.E5 AS 'TOTAL' FROM ( SELECT NAME, SUM(E1) E1 ,SUM(E2) E2 ,SUM(E3) E3 ,SUM(E4) E4 ,SUM(E5) E5 FROM #TABLE1 GROUP BY NAME)A
output
NAME E1 E2 E3 E4 E5 TOTAL
Jeni 1 5 2 0 3 11
Priya 10 3 7 0 3 23
#2
3
The thing you are looking for is a Group by .
你要找的东西是Group by。
To get the result you are looking for , i just made the table you described and did a group by and did a sum on the integer values.
为了获得您正在寻找的结果,我只是制作了您描述的表并进行了分组,并对整数值进行了总结。
select name, sum(E1),sum(E2),sum(E3),sum(E4),sum(E5) from Test Group by name
#3
0
;With cte(Name,E1,E2,E3,E4,E5)
AS
(
SELECT 'Jeni' ,1 ,0 , 0, 0, 0 Union all
SELECT 'Jeni' ,0 ,0 , 2, 0, 0 Union all
SELECT 'Jeni' ,0 ,5 , 0, 0, 3 Union all
SELECT 'Priya',0 ,3 , 0, 0, 0 Union all
SELECT 'Priya',0 ,0 , 0, 0, 3 Union all
SELECT 'Priya',0 ,0 , 7, 0, 0 Union all
SELECT 'Priya',10,0 , 0, 0, 0
)
SELECT Name,E1,E2,E3,E4,E5,Total FROM
(
SELECT *,Row_number()Over(Partition by Name order by Name)AS Seq From
(
SELECT Name,SUM(E1)E1,
SUM(E2)E2,
SUM(E3)E3,
SUM(E4)E4,
SUM(E5)E5,
SUM(E1+E2+ E3+ E4+E5) AS Total
FROM cte
Group by Name
)Dt
)AS Final
WHERE Final.Seq=1