本文介绍了如何在SQL数据库中合并单行中的两行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
先生,我有一个sql表,如下所示: -
NAME | SALARY | MONTHAshish | 2500 | 2月
Ashish | 3000 | Mar
i需要此表的结果如下: - NAME | SALARY | MONTH
Ashish | 5500 | 2月,3月 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', 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 | MONTHAshish| 2500| Feb
Ashish| 3000| Mar
i need a result of this table like:-
NAME | SALARY | MONTHAshish| 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数据库中合并单行中的两行
发布评论