借用,学习一下,我也create Table #TEST (id nvarchar(10),one nvarchar(10),two nvarchar(10)) Insert #TEST Values('1',N'aaa',N'a') Insert #TEST Values('1',N'bbb',N'b') Insert #TEST Values('1',N'ccc',N'c') Insert #TEST Values('2',N'ddd',N'd')select id,stuff((select ','+one from #TEST where id=t.id for xml path('')),1,1,'') ,stuff((select ','+two from #TEST where id=t.id for xml path('')),1,1,'') from #TEST t group by id
这个如何。SQL2000也可以用。 --定义临时表 Declare @tbTemp As Table( UserID Int Not Null, Record Varchar(Max) Default '', Other Varchar(Max) Default '' )--Select * From @tbTempDeclare @varUid As Int Declare @varRecord As Varchar(Max); Set @varRecord = '' Declare @varOther As Varchar(Max); Set @varOther = '' Declare @varCount As Int; Set @varCount = 0 Declare cur As Cursor For Select Uid, Jilu, Qita From MyTable Open cur Fetch Next cur Into @varUid, @varRecord, @varOther While @@Fetch_Status = 0 Begin Set @varCount = ( Select Count(UserID) From @tbTemp ) If (@varCount = 0) Begin Insert Into @tbTemp ( @varUid, @varRecord, @varOther ) End Else Begin Update @tbTemp Set Record = Record + ',' + @varRecord, Other = Other + ',' + @varOther Where UserID = @varUid End Fetch Next cur Into @varUid, @varRecord, @varOther End Close cur Deallocate cur
SELECT DISTINCT a.uid, stuff ((SELECT ',' + jilu FROM tab_jl WHERE uid = a.uid ORDER BY uid FOR xml path('')), 1, 1, '') jilu, stuff ((SELECT ',' + qita FROM tab_jl WHERE uid = a.uid ORDER BY uid FOR xml path('')), 1, 1, '') qita FROM tab_jl a
想起来这个帖子还没有结贴,今天来把正确解法告诉大家: SQL Union和SQL Union All用法 UNION 操作符用于合并两个或多个 SELECT 语句的结果集。 请注意,UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同。 SQL UNION 语法 SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2 注释:默认地,UNION 操作符选取不同的值,即UNION是去了重的。如果允许重复的值,请使用 UNION ALL。 SQL UNION ALL 语法 SELECT column_name(s) FROM table_name1 UNION ALL SELECT column_name(s) FROM table_name2 另外,UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。 详细见:http://www.cnblogs.com/fxgachiever/archive/2010/09/10/1823057.html
借用,学习一下,我也create Table #TEST
(id nvarchar(10),one nvarchar(10),two nvarchar(10))
Insert #TEST Values('1',N'aaa',N'a')
Insert #TEST Values('1',N'bbb',N'b')
Insert #TEST Values('1',N'ccc',N'c')
Insert #TEST Values('2',N'ddd',N'd')select id,stuff((select ','+one from #TEST where id=t.id for xml path('')),1,1,'')
,stuff((select ','+two from #TEST where id=t.id for xml path('')),1,1,'')
from #TEST t group by id
--定义临时表
Declare @tbTemp As Table(
UserID Int Not Null,
Record Varchar(Max) Default '',
Other Varchar(Max) Default ''
)--Select * From @tbTempDeclare @varUid As Int
Declare @varRecord As Varchar(Max); Set @varRecord = ''
Declare @varOther As Varchar(Max); Set @varOther = ''
Declare @varCount As Int; Set @varCount = 0
Declare cur As Cursor For Select Uid, Jilu, Qita From MyTable
Open cur
Fetch Next cur Into @varUid, @varRecord, @varOther
While @@Fetch_Status = 0 Begin
Set @varCount = (
Select Count(UserID) From @tbTemp
)
If (@varCount = 0) Begin
Insert Into @tbTemp (
@varUid, @varRecord, @varOther
)
End
Else Begin
Update @tbTemp Set Record = Record + ',' + @varRecord, Other = Other + ',' + @varOther
Where UserID = @varUid
End
Fetch Next cur Into @varUid, @varRecord, @varOther
End
Close cur
Deallocate cur
((SELECT ',' + jilu
FROM tab_jl
WHERE uid = a.uid
ORDER BY uid FOR xml path('')), 1, 1, '') jilu, stuff
((SELECT ',' + qita
FROM tab_jl
WHERE uid = a.uid
ORDER BY uid FOR xml path('')), 1, 1, '') qita
FROM tab_jl a
不解释.....
SQL Union和SQL Union All用法
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。 请注意,UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同。 SQL UNION 语法
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2 注释:默认地,UNION 操作符选取不同的值,即UNION是去了重的。如果允许重复的值,请使用 UNION ALL。 SQL UNION ALL 语法
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2 另外,UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。
详细见:http://www.cnblogs.com/fxgachiever/archive/2010/09/10/1823057.html