|
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) |
|