How to use SQL statements to combine data in the same column into a row using a group by statement

I saw a post from the forum just now. I have encountered this kind of problem before, so I wrote it down and learned something new. Different codes provided by the respondent and multiple thinking solutions to the problem are also seen from the other side. There are still many things to learn.

Learned content: Stuff Function Application

Problem:

There is a user table (name, number, hobby)
User (name, ID, holobby ),
The data includes:
Michael Zhang 001 basketball
James 001 movie
Li Si 002 football
Wang wu003 surfing the internet
Li Si 002 beauty watching
Now we need to write an SQL statement so that the result is:
James 001 basketball, movie
Li Si 002 football, watching beautiful women
Wang wu003 surfing the internet

 

Solution:

Solution 1:

SQL code

   If object_id ('user') is not null drop table [user] gocreate table [user] (Name varchar (10), Id varchar (5), Hober varchar (10 )) insert into [user] Select 'zhang san', '001', 'basketball 'Union allselect' Zhang san', '001', 'cine' Union allselect 'Li si', '002 ', 'soccer 'Union allselect' Wang wu', '003', 'Surfing the net' Union allselect' Li si', '002 ', 'look at the beauty' select name, ID, stuff (select ',' + holobby from [user] T2 where t2.name = t1.name for XML Path (''), 1, 1,'') from [user] t1group by name, ID/* name ID ---------- ----- --------------- Li si002 football, watch movie */

Solution 2:
 
 

  
  

    SQL code 
  
  
  
 
   
    -- SQL2000--1. create Table Tb (name varchar (10), Id int, holobby varchar (20) insert into TB select 'zhang san', '001 ', 'basketball 'Union allselect' Michael ', '001', 'cine' Union allselect' Lee 4', '002', 'soccer 'Union allselect' Wang wu', '003 ', 'internet' Union allselect' Li si', '002', 'look at the beauty 'gocreate function DBO. f_str (@ id int) returns varchar (8000) asbegin declare @ r varchar (8000) set @ r = ''select @ r = @ r + ', '+ holobby from TB where id = @ ID return stuff (@ r, 1, 1, '') endgo -- call the Select name, holobby = DBO function. f_str (ID) from TB group by name, iddrop table tbdrop function DBO. f_str/* (the number of lines affected is 5 rows) Name hobian Li Si football, watch movie (the number of lines affected is 3 rows )*/