是这样的, 假如有2个Field
Fld1 Fld2
A STR1
A STR2
A STR3
B STR4
B STR5
得出下面的结果
Fld1 Fld2
A STR1+STR2+STR3
B STR4+STR5
Fld1 Fld2
A STR1
A STR2
A STR3
B STR4
B STR5
得出下面的结果
Fld1 Fld2
A STR1+STR2+STR3
B STR4+STR5
RETURNS VARCHAR(500)
AS
DECLARE @SQL NVARCHAR(500)
SELECT @SQL=ISNULL(@SQL+'+','')+FID2 FROM TB WHERE FLD1=@FLD1
RETURN @SQLSELECT FLD1,DBO.GET_STRING(FLD1)AS FLD2 FROM TB
Fld1,
Fld2=stuff((select '+'Fld2 from tb where Fld1=t.Fld for xml path('')),1,1,'')
from
tb t
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-11-13 11:20:19
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Fld1] varchar(1),[Fld2] varchar(4))
insert [tb]
select 'A','STR1' union all
select 'A','STR2' union all
select 'A','STR3' union all
select 'B','STR4' union all
select 'B','STR5'
--------------开始查询--------------------------
select [Fld1], [Fld2]=stuff((select '+'+[Fld2] from tb t where [Fld1]=tb.[Fld1] for xml path('')), 1, 1, '')
from tb
group by [Fld1]
----------------结果----------------------------
/* Fld1 Fld2
---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A STR1+STR2+STR3
B STR4+STR5(2 行受影响)
*/
---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Fld1] varchar(1),[Fld2] varchar(4))
insert [tb]
select 'A','STR1' union all
select 'A','STR2' union all
select 'A','STR3' union all
select 'B','STR4' union all
select 'B','STR5'
---查询---
select
Fld1,
Fld2=stuff((select '+'+Fld2 from tb where Fld1=t.Fld1 for xml path('')),1,1,'')
from
tb t
group by Fld1
---结果---
Fld1 Fld2
---- -------------------
A STR1+STR2+STR3
B STR4+STR5(所影响的行数为 2 行)
create table temp
(
fld1 varchar(8),
fld2 varchar(8)
)
goinsert into temp
select 'A','STR1' UNION ALL
SELECT 'A','STR2' UNION ALL
SELECT 'A','STR3' UNION ALL
SELECT 'B','STR4' UNION ALL
SELECT 'B','STR5'
GOcreate FUNCTION My_Cross
(
@id varchar(8)
)
returns varchar(max)
as
begin
declare @str varchar(max)
select @str='';
select @str=@str+'+'+fld2 from temp where fld1=@id
select @str=stuff(@str,1,1,'')
return @str
endselect fld1,dbo.My_Cross(fld1) from temp group by fld1
想变成Fld1 Fld2
A STR1
STR2
STR3
B STR4
STR5
WH:PM(XIANGFENG) (8000 PCS)GF8A/BU1
WH:PM(XIANGFENG) (8000 PCS)GF8A/BU1
WH:PM(XIANGFENG) (8000 PCS)GF8A/BU1
go
create table [tb]([Fld1] varchar(1),[Fld2] varchar(4))
insert [tb]
select 'A','STR1' union all
select 'A','STR2' union all
select 'A','STR3' union all
select 'B','STR4' union all
select 'B','STR5'ALTER FUNCTION GET_STRING(@FLD1 VARCHAR(10))
RETURNS VARCHAR(500)
AS
BEGIN
DECLARE @SQL NVARCHAR(500)
SELECT @SQL=ISNULL(@SQL+CHAR(10)+' ',' ')+FLD2+' ' FROM TB WHERE FLD1=@FLD1
RETURN @SQL
ENDSELECT FLD1,DBO.GET_STRING(FLD1)AS FLD2 FROM TB GROUP BY FLD1FLD1 FLD2
---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A STR1
STR2
STR3
B STR4
STR5 (所影响的行数为 2 行)
go
create table [tb]([Fld1] varchar(1),[Fld2] varchar(4))
insert [tb]
select 'A','STR1' union all
select 'A','STR2' union all
select 'A','STR3' union all
select 'B','STR4' union all
select 'B','STR5'ALTER FUNCTION GET_STRING(@FLD1 VARCHAR(10))
RETURNS VARCHAR(500)
AS
BEGIN
DECLARE @SQL NVARCHAR(500)
SELECT @SQL=ISNULL(@SQL+CHAR(10)+' ',' ')+FLD2+' ' FROM TB WHERE FLD1=@FLD1
RETURN @SQL
ENDSELECT FLD1,DBO.GET_STRING(FLD1)AS FLD2 FROM TB GROUP BY FLD1FLD1 FLD2
---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A STR1
STR2
STR3
B STR4
STR5 (所影响的行数为 2 行)
declare @tb table ([Fld1] varchar(1),[Fld2] varchar(4))
insert @tb
select 'A','STR1' union all
select 'A','STR2' union all
select 'A','STR3' union all
select 'B','STR4' union all
select 'B','STR5'select [Fld1], [Fld2]=(select [Fld2] from @tb t where [Fld1]=tb.[Fld1] for xml path(''))
from @tb tb
group by [Fld1] select [Fld1], [Fld2]=(select '+'+[Fld2] from @tb t where [Fld1]=tb.[Fld1] for xml path(''))
from @tb tb
group by [Fld1]
(5 row(s) affected)
Fld1 Fld2
---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A <Fld2>STR1</Fld2><Fld2>STR2</Fld2><Fld2>STR3</Fld2>
B <Fld2>STR4</Fld2><Fld2>STR5</Fld2>(2 row(s) affected)Fld1 Fld2
---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A +STR1+STR2+STR3
B +STR4+STR5(2 row(s) affected)为什么前面多了个( '+'+ ), 结果就完全不一样了呢?