本帖最后由 smallblind 于 2013-2-16 16:17 编辑
只适用于sql2005以上版本，不支持2000，因为2000不支持 for xml用法create table TESTA
(
ID INT PRIMARY KEY
)
GO
CREATE TABLE TESTB
(
ID INT FOREIGN KEY REFERENCES TESTA(ID),
BZ CHAR(10)
)
GO
INSERT INTO TESTA
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
INSERT INTO TESTB
SELECT 1,'11' UNION ALL SELECT 1,'12'
UNION ALL SELECT 1,'13' UNION ALL SELECT 2,'21' UNION ALL SELECT 2,'22'
UNION ALL SELECT 3,'31' UNION ALL SELECT 3,'32'
SELECT B.ID as 处理号,ISNULL(LEFT(BZ,LEN(BZ)),'') as 备注 FROM (
SELECT ID,
(SELECT BZ+' ' FROM TESTB
WHERE ID=A.ID
FOR XML PATH('')) AS BZ
FROM TESTA A
GROUP BY ID
) B |
