如何在SQL数据库中合并单行中的两行 [英] How to merge two row in a single row in SQL database
本文介绍了如何在SQL数据库中合并单行中的两行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
先生,我有一个sql表,如下所示: -
NAME | SALARY | MONTH
Ashish | 2500 | 2月
Ashish | 3000 | Mar
i需要此表的结果如下: -
< pre lang =SQL> NAME | SALARY | MONTH
Ashish | 5500 | 2月,3月$ $ p> p>
我的尝试:
i我正在尝试查询,但它不适合我:
SELECT [名称],总和(薪水)
,STUFF( (SELECT','+ A.Month FROM Table1 A
其中A.Name = B.Table1 FOR XML PATH('')),1,1,'')As [Month]
来自表1 B
分组按[名称] 解决方案
尝试
声明 @ table table
(
NAME varchar ( 50 ),
SALARY int ,
MONTH varchar ( 250 )
)
插入 进入 @ table (NAME,SALARY,MONTH)值(' aa', 2000 ,' Jan')
insert into @ table (NAME,SALARY,MONTH) values (' aa', 3000 ,' 2月')
insert into @ table (NAME,SALARY,MONTH )值(' bb',< span class =code-digit> 1000 ,' Mar')
insert into @ table (NAME,SALARY,MONTH) values (' bb', 3000 , ' Apr')
insert 进入 @ table (NAME,SALARY,MONTH)值(' bb', 2500 ,' Jun')
SELECT 名称,薪水=总和(薪水),月=
STUFF(( SELECT ' ,' + MONTH
FROM @ table b
WHERE b.NAME = a.NAME
FOR XML PATH(' ')), 1 , 2 ,' ')
FROM @ table a
GROUP BY NAME
sir i have a sql table thats look like:-
NAME | SALARY | MONTH
Ashish| 2500| Feb
Ashish| 3000| Mar
i need a result of this table like:-
NAME | SALARY | MONTH
Ashish| 5500| Feb,Mar
What I have tried:
i am trying query but its not working for me:
SELECT [Name],Sum(Salary)
, STUFF((SELECT ', ' + A.Month FROM Table1 A
Where A.Name=B.Table1 FOR XML PATH('')),1,1,'') As [Month]
From Table1 B
Group By [Name]
解决方案 try
declare @table table
(
NAME varchar(50),
SALARY int,
MONTH varchar(250)
)
insert into @table(NAME,SALARY,MONTH)values( 'aa',2000,'Jan')
insert into @table(NAME,SALARY,MONTH)values( 'aa',3000,'Feb')
insert into @table(NAME,SALARY,MONTH)values( 'bb',1000,'Mar')
insert into @table(NAME,SALARY,MONTH)values( 'bb',3000,'Apr')
insert into @table(NAME,SALARY,MONTH)values( 'bb',2500,'Jun')
SELECT Name, Salary = sum(salary), Month =
STUFF((SELECT ', ' + MONTH
FROM @table b
WHERE b.NAME = a.NAME
FOR XML PATH('')), 1, 2, '')
FROM @table a
GROUP BY NAME
这篇关于如何在SQL数据库中合并单行中的两行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文