MSSQL · merges one to many data into data with specified separator

Time:2021-6-9
Reading time |0.05 min word count |142.4 characters
primary coverage |1. Introduction & background
“MSSQL · merge one to many data into data with specified separator”
Author | SCscHero Writing time | 2021/5/16 PM1:25
Article type |Series Completion |Completed
motto Every great cause has a trivial beginning.

1、 Introduction & background    Completion: 100%


a) Coping with problems

  1. How to merge data into one row according to a certain field?
  2. How to merge one to many data into one to one row data separated by commas?

b) Application scenarios

As shown in the figure above, a name corresponds to multiple products, and each product corresponds to a different amount, which needs to be converted into data similar to that in the figure below.

MSSQL · merges one to many data into data with specified separator

2、 Stuff function syntax & simulation scene    Completion: 100%


  • The syntax of the stuff function is as follows:
Stuff(character_expression,start,length,character_expression)
1. character_expression char
2. An expression of character data to be deleted. character_expression can be a constant, variable, or column of either character or binary data.
  • Simulation scene
--1. Simulation scene
create table #User_Game_MoneySum(
name nvarchar(10) NULL
,product Nvarchar(100) NULL
,amount BIGINT
);
 
insert into #User_ Game_ Moneysum values ('scscuro ','starcraft 1','233 ');
insert into #User_ Game_ Moneysum values ('scscuro ','starcraft 2','1500 ');
insert into #User_Game_MoneySum values ('SCscHero','GTA5','99');
insert into #User_ Game_ Moneysum values ('scs hero ','rainbow 6','230 ');
insert into #User_ Game_ Moneysum values ('scschero ','diablo','400 ');
insert into #User_ Game_ Moneysum values ('scscuro ','warcraft 3','239 ');
insert into #User_ Game_ Moneysum values ('mitt Miller ',' after tomorrow ',' 500 ');
insert into #User_ Game_ Moneysum values ('mitt Miller ',' StarCraft 2 ',' 1000 ');
insert into #User_ Game_ Moneysum values ('starcraft storm ','starcraft 2','0 ');
insert into #User_ Game_ Moneysum values ('starspirit storm ','cs: go','20 ');
insert into #User_ Game_ Moneysum values ('elephant in the room ',' Jedi survive ',' 100 ');
insert into #User_ Game_ Moneysum values ('elephant in the room ','starcraft 2','0 ');

select * from #User_Game_MoneySum; 

--2. Grouping
select name
		,products = STUFF(
						(select ','+product 
						from #User_Game_MoneySum b
						where a.name = b.name 
							for xml path(''))
						,1
						,1
						,'')
		 ,sum(amount) as amount
  from #User_Game_MoneySum a
  group by name;

DROP TABLE #User_Game_MoneySum

3、 Statement and references    Completion: 100%


Original blog, please do not reprint without permission.

If you have any help, you are welcome to like, collect and pay attention. If you have any questions, please comment! If you need to contact the blogger, you can directly send a private message to scscshero.