暴难的字符串拼接问题（不可以用临时表 及 游标）
现在有表：
FieldValue ConcatenationOrder
-------------------------
TH NULL
LF NULL
1N4148-TAP NULL
1 1
VF 1
0.3 2
A 2
80 4
VBR 4
DO35 NULL
****************************************************************
需要拼接字符串结果如下：
------------------------------------------------
TH,LF,1N4148-TAP,1VF,0.3A,80VBR,DO35
****************************************************************
规则：
如果ConcatenationOrder字段为NULL,用逗号（,）格开，如果不为null,字符直接连接，但不同的ConcatenationOrder字段，其FieldValue需用逗号(,)格开。
不可以用临时表，不可以用游标。其他应该都可以使用。
****************************************************************
现在有一个比较接近的语句可以参考，或者推翻重新写也可以。
declare @str varchar(8000)
set @str= ' '
select @str=@str+ ', ' +cast(FieldValue as varchar) from
(
select * from testtable
) x
set @str=right(@str,len(@str)-1)
select @str
****************************************************************
表结构数据提供：
if exists (select * from dbo.sysobjects where id = object_id(N '[TestTable] ') and OBJECTPROPERTY(id, N 'IsUserTable ') = 1)
drop table [TestTable]
GO
create table TestTable(
FieldValue varchar(80),
ConcatenationOrder int
)
GO
insert into TestTable
select 'TH ',NULL
union select 'LF ', NULL
union select '1N4148-TAP ',NULL
union select '1 ',1
union select 'VF ',1
union select '0.3 ',2
union select 'A ',2
union select '80 ',4
union select 'VBR ',4
union select 'DO35 ',NULL
Go
select * from testtable
GO
[解决办法]
SELECT
FieldValue = REPLACE(re.value( '(/r)[1] ', 'nvarchar(max) '), N ' , ', N ' ')
FROM(
SELECT re = (
SELECT *
FROM(
SELECT FieldValue
FROM testtable
WHERE ConcatenationOrder IS NULL
UNION ALL
SELECT
FieldValue = REPLACE(B.FieldValue.value( '(/r)[1] ', 'nvarchar(max) '), N ' ', N ' ')
FROM(
SELECT DISTINCT
ConcatenationOrder
FROM testtable
WHERE ConcatenationOrder IS NOT NULL
)A
CROSS APPLY(
SELECT FieldValue =(
SELECT v = FieldValue
FROM testtable t
WHERE ConcatenationOrder = A.ConcatenationOrder
FOR XML AUTO, TYPE
).query( ' <r> {for $i in /t/@v return(string($i))} </r> ')
)B
)AA
FOR XML AUTO, TYPE
).query( ' <r> {for $i in /AA/@FieldValue return(concat( ", ",string($i)))} </r> ')
)AAA
[解决办法]
--试下这个
create function fn_str(@s varchar(2))
returns varchar(8000)
as
begin
declare @t varchar(4000)
select @t = ' '
--当ConcatenationOrder是NULL
if @s is null
begin
select @t = @t +FieldValue+ ', '
from testtable
where ConcatenationOrder is null
end
--当ConcatenationOrder是NOT NULL
select @t = @t + FieldValue
from testtable
where ConcatenationOrder = @s
--删除多余的 ', '
select @t = case when @s is not null then @t + ', ' else @t end
return @t
end
--执行
declare @ss varchar(4000)
select @ss = ' '
select @ss = @ss + dbo.fn_str(ConcatenationOrder) from testtable
group by ConcatenationOrder
order by ConcatenationOrder
select left(@ss,len(@ss) - 1)
--结果
1N4148-TAP,DO35,LF,TH,1VF,0.3A,80VBR
[解决办法]
--修改表結構，在表上加上一個自增列，可以實現樓主的需求。
if exists (select * from dbo.sysobjects where id = object_id(N '[TestTable] ') and OBJECTPROPERTY(id, N 'IsUserTable ') = 1)
drop table [TestTable]
GO
create table TestTable(
FieldValue varchar(80),
ConcatenationOrder int
)
GO
insert into TestTable
select 'TH ',NULL
union All select 'LF ', NULL
union All select '1N4148-TAP ',NULL
union All select '1 ',1
union All select 'VF ',1
union All select '0.3 ',2
union All select 'A ',2
union All select '80 ',4
union All select 'VBR ',4
union All select 'DO35 ',NULL
Go
--加上一個自增列
Alter Table testtable Add ID Int Identity(1, 1)
GO
Create Function F_GetFieldValue(@ConcatenationOrder Int)
Returns Varchar(8000)
As
Begin
Declare @S Varchar(8000)
Select @S = ' '
Select @S = @S + FieldValue From testtable Where ConcatenationOrder = @ConcatenationOrder
Return @S
End
GO
Create Function F_TEST()
Returns Varchar(8000)
As
Begin
Declare @S Varchar(8000)
Select @S = ' '
Select @S = @S + ', ' + FieldValue
From
(
Select ID, FieldValue From testtable Where ConcatenationOrder Is Null
Union
Select Min(ID), dbo.F_GetFieldValue(ConcatenationOrder) As FieldValue From testtable Where ConcatenationOrder Is Not Null Group By ConcatenationOrder
) A
Order By ID
Return Stuff(@S, 1, 1, ' ')
End
GO
Select dbo.F_TEST() As FieldValue
Alter Table testtable Drop Column ID
GO
Drop Table testtable
Drop Function F_GetFieldValue, F_TEST
GO
--Result
/*
FieldValue
TH,LF,1N4148-TAP,1VF,0.3A,80VBR,DO35
*/
[解决办法]
--yeah,一定要排序,比如id字段
GO
create function fn_test(@ConcatenationOrder int)
returns varchar(500)
AS
begin
declare @str varchar(500)
set @str= ' '
select @str=@str +FieldValue from TestTable
where ConcatenationOrder =@ConcatenationOrder
return @str
end
Go
if exists (select * from dbo.sysobjects where id = object_id(N '[TestTable] ') and OBJECTPROPERTY(id, N 'IsUserTable ') = 1)
drop table [TestTable]
GO
create table TestTable(
FieldValue varchar(80),
ConcatenationOrder int
)
GO
insert into TestTable
select 'TH ',NULL
insert into TestTable select 'LF ', NULL
insert into TestTable select '1N4148-TAP ',NULL
insert into TestTable select '1 ',1
insert into TestTable select 'VF ',1
insert into TestTable select '0.3 ',2
insert into TestTable select 'A ',2
insert into TestTable select '80 ',4
insert into TestTable select 'VBR ',4
insert into TestTable select 'DO35 ',NULL
Go
declare @s varchar(1000)
set @s= ' '
select @s=@s+ ', '+FieldValue
from
(
select distinct isnull(B.FieldValue,A.FieldValue) as FieldValue
from testtable A left join
(select dbo.fn_test(ConcatenationOrder) as FieldValue,ConcatenationOrder from testtable
where ConcatenationOrder is not null group by ConcatenationOrder) B
on A.ConcatenationOrder=B.ConcatenationOrder
) T
select @s=stuff(@s,1,1, ' ')
select @s
/*SQL默認的就這個樣子了*/
/* 0.3A,1N4148-TAP,1VF,80VBR,DO35,LF,TH */
drop table testtable
drop function fn_test
[解决办法]
--再改下
DECLARE @SQL VARCHAR(8000)
DECLARE @STA INT
SELECT @SQL= ' '
select @SQL=CASE WHEN ConcatenationOrder IS NOT NULL THEN
CASE WHEN @STA = ConcatenationOrder THEN @SQL+FieldValue
ELSE @SQL+ ', '+ FieldValue END
ELSE @SQL+ ', '+ FieldValue
END,@STA=ConcatenationOrder
from TestTable
SELECT RIGHT(@SQL,LEN(@SQL)-1)
