Query of SQL column merge

Table
Code name
1
1 B
1 c
2
2
2 D
3 D
3 C
3 E
......

Want to get:
Code name
1 a, B, c
2 A, D
3 C, D, E
.....
Thank you for your help !!!
-- Reference

String summary problem. The recommended method for SQL Server 2000 is: User-defined functions. For specific solutions and implementation, see:
Bytes ---------------------------------------------------------------------------------------
-- Generate Test Data
Create Table (Department int, personnel varchar (20 ))
Insert into Table select 1, 'zhang san'
Insert into Table select 1, 'Li si'
Insert into Table select 1, 'wang wu'
Insert into Table select 2, 'zhao liu'
Insert into Table select 2, 'deng 7'
Insert into Table select 2, 'Liu Ba'
Go

-- Create a user-defined function
Create Function f_str (@ Department INT)
Returns varchar (8000)
As
Begin
Declare @ RET varchar (8000)
Set @ ret =''
Select @ ret = @ RET + ',' + personnel from table where Department = @ Department
Set @ ret = stuff (@ ret, 1, 1 ,'')
Return @ RET
End
Go

-- Execute
Select department, personnel = DBO. f_str (department) from Table group by Department Order by department
Go

-- Output result
/*
Department staff
------------------
1 Zhang San, Li Si, Wang Wu
2 Zhao Liu, Deng Qi, and Liu Ba
*/

-- Delete test data
Drop function f_str
Drop table
Go
------------------------------------------------------------------------

Create Function rowtocol (@ id int)
Returns varchar (1000)
As
Begin
Declare @ STR varchar (1000)
Set @ STR =''
Select @ STR = @ STR + ',' + name from Table A where code = @ ID
Set @ STR = staff (@ STR, 1,1 ,'')
Return @ Str
End

Select Code, DBO. rowtocol (CODE) from Table A group by code

---------------------------------------------------------------------------

Create Table A (codenamed int, name varchar (20 ))
Insert into Table A select 1, 'A'
Insert into Table A select 1, 'B'
Insert into Table A select 1, 'C'
Insert into Table A select 2, 'A'
Insert into Table A select 2, 'A'
Insert into Table A select 2, 'D'
Insert into Table A select 3, 'D'
Insert into Table A select 3, 'C'
Insert into Table A select 3, 'E'
Go

Drop function rowtocol
Create Function rowtocol (@ id int)
Returns varchar (1000)
As
Begin
Declare @ STR varchar (1000)
Set @ STR =''
Select @ STR = @ STR + ',' + name from Table A where code = @ ID
Set @ STR = stuff (@ STR, 1,1 ,'')
Return @ Str
End

Select Code, DBO. rowtocol (CODE) name from Table A group by code
-----------------------------------------------------------------------------

-- Merge and convert columns with symbols

-- The table t has the following data:
A B
1 1
1 2
1 3
2 1
2 2
3 1
-- Convert to the following result:
A B
1 1, 2, 3
2 1, 2
3 1

Create Table TB
(
A int,
B INT
)
Insert into Tb (a, B) values (1, 1)
Insert into Tb (a, B) values (1, 2)
Insert into Tb (a, B) values (1, 3)
Insert into Tb (a, B) values (2, 1)
Insert into Tb (a, B) values (2, 2)
Insert into Tb (a, B) values (3, 1)
Go

If object_id ('pubs .. f_hb ') is not null
Drop function f_hb
Go

-- Create a merged Function
Create Function f_hb (@ a int)
Returns varchar (8000)
As
Begin
Declare @ STR varchar (8000)
Set @ STR =''
Select @ STR = @ STR + ',' + Cast (B as varchar) from TB where a = @
Set @ STR = right (@ STR, Len (@ Str)-1)
Return (@ Str)
End
Go

-- Call the custom function to obtain the result:
Select distinct A, DBO. f_hb (a) as B from TB

Drop table TB

-- Result
A B
-----------------
1 1, 2, 3
2 1, 2
3 1

(The number of affected rows is 3)

Merge multiple leading fields
The original data status is as follows:
Id PR Con Op SC
001 p c difference 6
001 p c 2
001 p c generally 4
002 w e difference 8
002 w e 7
002 w e average 1
======================================
Use SQL statements to convert the data into the following:
Id PR con ops
001 p c difference (6), good (2), general (4)
002 w e difference (8), good (7), General (1)

If object_id ('pubs .. tb') is not null
Drop table TB
Go

Create Table TB
(
Id varchar (10 ),
PR varchar (10 ),
Con varchar (10 ),
OP varchar (10 ),
SC int
)

Insert into Tb (ID, PR, Con, op, SC) values ('001', 'P', 'C', 'differential ', 6)
Insert into Tb (ID, PR, Con, op, SC) values ('001', 'P', 'C', 'good', 2)
Insert into Tb (ID, PR, Con, op, SC) values ('001', 'P', 'C', '001', 4)
Insert into Tb (ID, PR, Con, op, SC) values ('002 ', 'w', 'E', 'differential', 8)
Insert into Tb (ID, PR, Con, op, SC) values ('002 ', 'w', 'E', 'ha', 7)
Insert into Tb (ID, PR, Con, op, SC) values ('002 ', 'w', 'E', 'General', 1)
Go

If object_id ('pubs .. test') is not null
Drop Table Test
Go
Select ID, PR, Con, Ops = OP + '(' + Cast (SC as varchar (10) + ')' into test from TB

-- Create a merged Function
If object_id ('pubs .. f_hb ') is not null
Drop function f_hb
Go
Create Function f_hb (@ ID varchar (10), @ PR varchar (10), @ con varchar (10 ))
Returns varchar (8000)
As
Begin
Declare @ STR varchar (8000)
Set @ STR =''
Select @ STR = @ STR + ',' + Cast (OPS as varchar) from test where id = @ ID and @ Pr = Pr and @ con = con
Set @ STR = right (@ STR, Len (@ Str)-1)
Return (@ Str)
End
Go

-- Call the custom function to obtain the result:
Select distinct ID, PR, Con, DBO. f_hb (ID, PR, con) as ops from test

Drop table TB
Drop Table Test

-- Result
Id PR con ops
-------------------------------------------------
001 p c difference (6), good (2), general (4)
002 w e difference (8), good (7), General (1)

(The number of affected rows is 2)
-----------------------------------------------------

Create Table TA (ID int, username varchar (20 ))
Insert into ta select 1, 'A'
Insert into ta select 2, 'bb'
Insert into ta select 3, 'cc'
Insert into ta select 4, 'dd'
Insert into ta select 2, 'ee'
Insert into ta select 3, 'ff'
Insert into ta select 2, 'gg'

Create Function Test (@ id int)
Returns varchar (8000)
As
Begin
Declare @ s varchar (8000)
Select @ s = isnull (@ s, '') + ',' + username from Ta where id = @ ID
Return stuff (@ s, 1, 1 ,'')
End

Select distinct ID, DBO. Test (ID) username from Ta
Id Username
------------------------------
1 aa
2 BB, EE, GG
3 cc, FF
4 dd

(The number of affected rows is 4)
-- Drop table Ta
-- Drop function test
------------------------------------------------------------------

Create Table TA (codenamed int, name varchar (20 ))
Insert into ta select 1, 'A'
Insert into ta select 1, 'B'
Insert into ta select 1, 'C'
Insert into ta select 2, 'A'
Insert into ta select 2, 'A'
Insert into ta select 2, 'd'
Insert into ta select 3, 'd'
Insert into ta select 3, 'C'
Insert into ta select 3, 'E'

Create Function Test (@ id int)
Returns varchar (8000)
As
Begin
Declare @ s varchar (8000)
Select @ s = isnull (@ s, '') + ',' + name from Ta where code = @ ID group by code, name -- add a group
Return stuff (@ s, 1, 1 ,'')
End

Select distinct code, DBO. Test (code name) name from Ta

-- Drop table Ta
-- Drop function test
Code name
----------------------
1 a, B, c
2 A, D
3 C, D, E

(The number of affected rows is 3)

--------------------------------------------------------------------

-- Function

Create Table A (codenamed int, name varchar (10 ))
Insert a select 1, 'A'
Union all select 1, 'B'
Union all select 1, 'C'
Union all select 2, 'A'
Union all select 2, 'A'
Union all select 2, 'D'
Union all select 3, 'd'
Union all select 3, 'C'
Union all select 3, 'E'

Create Function fun (@ code INT)
Returns varchar (100)
As
Begin
Declare @ Re varchar (100)
Set @ Re =''
Select @ Re = @ Re + ',' + name from a where code = @ code group by name

Return (stuff (@ Re, 1, 1 ,''))
End

Select Code, name = DBO. Fun (CODE)
From
A
Group by code

-- Result
Code name
----------- Begin ---------------------------------------------------------------------------------------------------------------
1 a, B, c
2 A, D
3 C, D, E

(3 row (s) affected)
---------------------------------------------------

-- Temporary table

Create Table A (codenamed int, name varchar (10 ))
Insert a select 1, 'A'
Union all select 1, 'B'
Union all select 1, 'C'
Union all select 2, 'A'
Union all select 2, 'A'
Union all select 2, 'D'
Union all select 3, 'd'
Union all select 3, 'C'
Union all select 3, 'E'

Select Code, cast (name as varchar (100) as name into #
From
Group by code, name
Order by code

Declare @ code int, @ name varchar (100)
Update # A set
@ Name = case when code = @ code then @ name + ',' + name else name end,
@ Code = code,
Name = @ name

Select Code, name = max (name) from #
Group by code

-- Result
Code name
----------- Begin ---------------------------------------------------------------------------------------------------------------
1 a, B, c
2 A, D
3 C, D, E

(3 row (s) affected)