-- 1楼的说法是正确的,请看下面例子: create table t1(ajid int,lx varchar(20), mc varchar(20)); create table t2(ajid int, yg varchar(20), ygmc varchar(200), bg varchar(20), bgmc varchar(200));insert into t1(ajid, lx, mc) select 1, '原告', 'A' union all select 1, '被告', 'B' union all select 2, '原告', 'A' union all select 2, '被告', 'B' union all select 2, '被告', 'C' union all select 3, '原告', 'A' union all select 3, '原告', 'B' union all select 3, '被告', 'C' union all select 3, '被告', 'D';select * from t1;insert into t2(ajid,yg,ygmc,bg,bgmc) select a1.ajid, '原告' as yg, [ygmc]=STUFF((select ', '+mc from t1 b1 where b1.ajid=a1.ajid and b1.lx='原告' order by b1.mc for XML path('')),1,1,''), '被告' as bg, [bgmc]=STUFF((select ', '+mc from t1 b2 where b2.ajid=a1.ajid and b2.lx='被告' order by b2.mc for XML path('')),1,1,'')
from t1 a1 group by a1.ajid;alter table t2 add id int identity(1,1); select * from t2;
-- 再设置该表的属性为identity
create table t1(ajid int,lx varchar(20), mc varchar(20));
create table t2(ajid int, yg varchar(20), ygmc varchar(200), bg varchar(20), bgmc varchar(200));insert into t1(ajid, lx, mc)
select
1, '原告', 'A' union all select
1, '被告', 'B' union all select
2, '原告', 'A' union all select
2, '被告', 'B' union all select
2, '被告', 'C' union all select
3, '原告', 'A' union all select
3, '原告', 'B' union all select
3, '被告', 'C' union all select
3, '被告', 'D';select * from t1;insert into t2(ajid,yg,ygmc,bg,bgmc)
select a1.ajid, '原告' as yg, [ygmc]=STUFF((select ', '+mc from t1 b1
where b1.ajid=a1.ajid and b1.lx='原告'
order by b1.mc for XML path('')),1,1,''),
'被告' as bg, [bgmc]=STUFF((select ', '+mc from t1 b2
where b2.ajid=a1.ajid and b2.lx='被告'
order by b2.mc for XML path('')),1,1,'')
from t1 a1
group by a1.ajid;alter table t2 add id int identity(1,1);
select * from t2;