SELECT DISTINCT C.CODEGC,D.NAMEGC,C.ADDRESSGC,D.EXPLAINGC
FROM (SELECT A.CODEGC,STUFF((SELECT ' ' + B.ADDRESSGC FROM TEST AS B WHERE A.CODEGC = B.CODEGC FOR XML PATH('')),1,1,'' ) AS ADDRESSGC FROM TEST AS A GROUP BY A.CODEGC) AS C INNER JOIN TEST AS D
ON C.CODEGC = D.CODEGC
SELECT DISTINCT C.CODEGC,D.NAMEGC,C.ADDRESSGC,D.EXPLAINGC
FROM (SELECT A.CODEGC,STUFF((SELECT ' ' + B.ADDRESSGC FROM TEST AS B WHERE A.CODEGC = B.CODEGC FOR XML PATH('')),1,1,'' ) AS ADDRESSGC FROM TEST AS A GROUP BY A.CODEGC) AS C INNER JOIN TEST AS D
ON C.CODEGC = D.CODEGC
IF OBJECT_ID('TEST') > 0
DROP TABLE TEST
create table test
(
CODEGC VARCHAR(10),
NAMEGC VARCHAR(10),
ADDRESSGC VARCHAR(10),
EXPLAINGC VARCHAR(10)
)
GO
insert into test
select 'xxx01','name001','address001','explain001'
union all
select 'xxx01','name001','address005','explain001'
union all
select 'xxx02','name002','address002','explain002'
SELECT DISTINCT C.CODEGC,D.NAMEGC,C.ADDRESSGC,D.EXPLAINGC
FROM (SELECT A.CODEGC,STUFF((SELECT ' ' + B.ADDRESSGC FROM TEST AS B WHERE A.CODEGC = B.CODEGC FOR XML PATH('')),1,1,'' ) AS ADDRESSGC FROM TEST AS A GROUP BY A.CODEGC) AS C INNER JOIN TEST AS D
ON C.CODEGC = D.CODEGC
结果
xxx01 name001 address001 address005 explain001
xxx02 name002 address002 explain002
create table tb把这个移到MSSQL版块吧
(
codeGC varchar(50),
nameGC varchar(50),
addressGC varchar(50),
explainGC varchar(50)
)
insert into tb values('XXX001' ,'name001', 'address001' ,'explain001')
insert into tb values('XXX002' ,'name002', 'address002' ,'explain002')
insert into tb values('XXX003' ,'name003', 'address003' ,'explain003')
insert into tb values('XXX004' ,'name004', 'address004' ,'explain004')
insert into tb values('XXX001' ,'name001', 'address005' ,'explain001')
insert into tb values('XXX002' ,'name002', 'address006' ,'explain002')
insert into tb values('XXX003' ,'name003', 'address007' ,'explain003')
select codeGC, nameGC,explainGC, addressGC=stuff((select ' '+addressGC from tb t where codeGC=tb.codeGC and
nameGC =tb.nameGC and explainGC =tb.explainGC for xml path('')), 1, 1, '')
from tb
group by codeGC, nameGC,explainGC
/*
codeGC,nameGC,explainGC,addressGC
XXX001,name001,explain001,address001 address005
XXX002,name002,explain002,address002 address006
XXX003,name003,explain003,address003 address007
XXX004,name004,explain004,address004
(4 行受影响)
create table tb(codeGC varchar(20),nameGC varchar(20),addressGC varchar(20),explainGC varchar(20))
insert into tb select '001','name001','address001','explain001'
insert into tb select '002','name002','address002','explain002'
insert into tb select '003','name003','address003','explain003'
insert into tb select '004','name004','address004','explain004'
insert into tb select '001','name001','address005','explain001'
insert into tb select '002','name002','address006','explain002' --如果这行nameGC是 002
insert into tb select '003','name003','address007','explain003'
go
select codeGC,nameGC,stuff((select ','+addressGC from tb where codeGC=a.codeGC for xml path('')),1,1,'')addressGC,explainGC
from tb a group by codeGC,nameGC,explainGC
/*
codeGC nameGC addressGC explainGC
-------------------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------
001 name001 address001,address005 explain001
002 name002 address002,address006 explain002
003 name003 address003,address007 explain003
004 name004 address004 explain004
(4 行受影响)
*/
go
drop table tb
