从sql查询中的重复列中删除所有可用值中只有一列的重复coumn

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

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

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

;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

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