About the writing of the select query

jy03335754

I have a table: TEST (Menu_Name varchar (3), Action_Name varchar (4))
The information inside is shown as
Menu_Name Action_Name
 A01 New
 A01 Modification
 A01 delete
 A01 Audit
 B01 new
 B01 modification
 C01 delete
 C01 audit
Now I want to use a SELECT query to display the results as:
 Menu_Name Action_Name
   A01 Add; Modify; Delete; Review
   B01 added; modified
   C01 delete; review

Please enlighten one or two, grateful!

fanfan1980

create table T (Menu_Name varchar (3), Action_Name varchar (4))
insert T select 'A01', 'Add'
union all select 'A01', 'Modify'
union all select 'A01', 'Delete'
union all select 'A01', 'Audit'
union all select 'B01', 'Add'
union all select 'B01', 'Modify'
union all select 'C01', 'Delete'
union all select 'C01', 'Audit'

create function fun (@Menu_Name char (3))
returns varchar (100)
as
begin
declare @re varchar (1000)
set @ re = ''
select @ re = @ re + Action_Name + ';' from T where Menu_Name = @ Menu_Name
select @ re = left (@re, len (@re) -1)

return @re
end

select distinct Menu_Name, dbo.fun (Menu_Name) as Action_Name from T

--result
Menu_Name Action_Name
--------- ----------------------------------------- -------------------------------------------------- ---------
A01 Add; Modify; Delete; Review
B01 added; modified
C01 delete; review

(3 row (s) affected)

我是杀人狂

create table test (Menu_Name varchar (3), Action_Name varchar (4))
insert into test (Menu_Name, Action_Name) values ​​('A01', 'Add')
insert into test (Menu_Name, Action_Name) values ​​('A01', 'Modify')
insert into test (Menu_Name, Action_Name) values ​​('A01', 'Delete')
insert into test (Menu_Name, Action_Name) values ​​('A01', 'Audit')
insert into test (Menu_Name, Action_Name) values ​​('B01', 'Add')
insert into test (Menu_Name, Action_Name) values ​​('B01', 'Modify')
insert into test (Menu_Name, Action_Name) values ​​('C01', 'Delete')
insert into test (Menu_Name, Action_Name) values ​​('C01', 'Audit')
go
create function f_hb (@a varchar (10))
returns varchar (8000)
as
begin
  declare @str varchar (8000)
  set @str = ''
  select @str = @str + ';' + Action_Name from test where Menu_Name = @a
  set @str = right (@str, len (@str)-1)
  return (@str)
End
go


select distinct Menu_Name, dbo.f_hb (Menu_Name) as Action_Name from test

drop table test
drop function f_hb
result:
Menu_Name Action_Name
--------- -------------------
A01 Add; Modify; Delete; Review
B01 added; modified
C01 delete; review

(3 rows affected)

fanfan1980

--Temporary table method

select Menu_Name, Action_Name = cast (Action_Name as varchar (100)) into #T from T order by Menu_Name

declare @Menu_Name varchar (3), @Action_Name varchar (100)
update #T set
@ Action_Name = case when Menu_Name = @ Menu_Name then @Action_Name + ';' + Action_Name else Action_Name end,
@ Menu_Name = Menu_Name,
Action_Name = @ Action_Name

select Menu_Name, max (Action_Name) as Action_Name from #T group by Menu_Name

--result
Menu_Name Action_Name
--------- ----------------------------------------- -------------------------------------------------- ---------
A01 Add; Modify; Delete; Review
B01 added; modified
C01 delete; review

(3 row (s) affected)

恶魔右指

create table T (Menu_Name varchar (3), Action_Name varchar (4))
insert T select 'A01', 'Add'
union all select 'A01', 'Modify'
union all select 'A01', 'Delete'
union all select 'A01', 'Audit'
union all select 'B01', 'Add'
union all select 'B01', 'Modify'
union all select 'C01', 'Delete'
union all select 'C01', 'Audit'

alter table t add display column varchar (20)-new display column
go
declare @ta table (id int identity (1,1), Menu_Name varchar (3), Action_Name varchar (4))
insert @ta
select Menu_Name, Action_Name from T
while exists (select 1 from @ta)
begin
update t
set display column = isnull (display column + ',', '') + ta.Action_Name
from @ta ta, t
where t.Menu_Name = ta.Menu_Name and
not exists (select 1 from @ta where Menu_Name = ta.Menu_Name and id <ta.id)

delete ta
from @ta ta
where not exists (select 1 from @ta where Menu_Name = ta.Menu_Name and id <ta.id)
end
go
select distinct Menu_Name, show columns from t--query
go
alter table t drop column
Menu_Name display column
--------- --------------------
A01 Add, modify, delete, review
B01 New, modified
C01 delete, review

(3 rows affected)

恶魔右指

There are many methods, and it is the simplest to implement them by functions, and it is also very simple to implement without functions.