| |

VerySource

 Forgot password?
 Register
Search
View: 929|Reply: 5

About the writing of the select query

[Copy link]

1

Threads

1

Posts

2.00

Credits

Newbie

Rank: 1

Credits
2.00

 China

Post time: 2020-1-20 15:20:01
| Show all posts |Read mode
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!
Reply

Use magic Report

0

Threads

211

Posts

108.00

Credits

Newbie

Rank: 1

Credits
108.00

 China

Post time: 2020-1-29 12:45:02
| Show all posts
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)
Reply

Use magic Report

0

Threads

114

Posts

69.00

Credits

Newbie

Rank: 1

Credits
69.00

 China

Post time: 2020-1-29 16:18:01
| Show all posts
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)
Reply

Use magic Report

0

Threads

211

Posts

108.00

Credits

Newbie

Rank: 1

Credits
108.00

 China

Post time: 2020-1-29 17:18:02
| Show all posts
--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)
Reply

Use magic Report

0

Threads

93

Posts

46.00

Credits

Newbie

Rank: 1

Credits
46.00

 China

Post time: 2020-1-29 23:00:01
| Show all posts
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)
Reply

Use magic Report

0

Threads

93

Posts

46.00

Credits

Newbie

Rank: 1

Credits
46.00

 China

Post time: 2020-1-30 09:00:01
| Show all posts
There are many methods, and it is the simplest to implement them by functions, and it is also very simple to implement without functions.
Reply

Use magic Report

You have to log in before you can reply Login | Register

Points Rules

Contact us|Archive|Mobile|CopyRight © 2008-2023|verysource.com ( 京ICP备17048824号-1 )

Quick Reply To Top Return to the list