create table t
(
number int,
[content] nvarchar(10)
)
GO
insert into t values(1,'A')
insert into t values(1,'B')
insert into t values(1,'C')
insert into t values(2,'A')
insert into t values(2,'B')
insert into t values(3,'B')
insert into t values(3,'C')
insert into t values(4,'C')
insert into t values(4,'D')
insert into t values(5,'D')
insert into t values(5,'C')
insert into t values(6,'C')
insert into t values(6,'B')
GOselect distinct content from
(
select number,content = stuff((select '+'+content from t where number = a.number for xml path('')),1,1,'')
from t a
)T
http://topic.csdn.net/u/20090428/16/b2d55129-d2bf-40b5-83f7-46e389529a7e.html
IF OBJECT_ID('T')IS NOT NULL DROP TABLE T
GO
create table t
(
number int,
[content] nvarchar(10)
)
insert into t values(1,'A')
insert into t values(1,'B')
insert into t values(1,'C')
insert into t values(2,'A')
insert into t values(2,'B')
insert into t values(3,'B')
insert into t values(3,'C')
insert into t values(4,'C')
insert into t values(4,'D')
insert into t values(5,'D')
insert into t values(5,'C')
insert into t values(6,'C')
insert into t values(6,'B')
IF OBJECT_ID('DBO.F_STR')IS NOT NULL DROP FUNCTION DBO.F_STR
GO
CREATE FUNCTION dbo.f_str(@number int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r = ''
SELECT @r = @r + '+' + [content]
FROM t
WHERE number=@number ORDER BY NUMBER,[CONTENT]
RETURN STUFF(@r, 1, 1, '')
END
GO
SELECT DISTINCT DBO.F_STR(NUMBER) FROM T GROUP BY NUMBER
/*A+B+C
A+B
B+C
C+D
*/
(
number int,
[content] nvarchar(10)
)
GO
insert into t values(1,'A')
insert into t values(1,'B')
insert into t values(1,'C')
insert into t values(2,'A')
insert into t values(2,'B')
insert into t values(3,'B')
insert into t values(3,'C')
insert into t values(4,'C')
insert into t values(4,'D')
insert into t values(5,'D')
insert into t values(5,'C')
insert into t values(6,'C')
insert into t values(6,'B')
GOselect distinct content from
(
select number,content = stuff((select '+'+content from t where number = a.number order by content for xml path('') ),1,1,'')
from t a
)Tdrop table t/*
A+B
A+B+C
B+C
C+D
*/
(
number int,
[content] nvarchar(10)
)
GO
insert into t values(1,'A')
insert into t values(1,'B')
insert into t values(1,'C')
insert into t values(2,'A')
insert into t values(2,'B')
insert into t values(3,'B')
insert into t values(3,'C')
insert into t values(4,'C')
insert into t values(4,'D')
insert into t values(5,'D')
insert into t values(5,'C')
insert into t values(6,'C')
insert into t values(6,'B')
GO CREATE FUNCTION F_COMBINESTR(@ID int)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @SQL VARCHAR(100)
SET @SQL=''
SELECT @SQL=@SQL+'+' + [content]
FROM (SELECT DISTINCT [content] FROM t WHERE number=@ID) T
ORDER BY [content]
RETURN STUFF(@SQL, 1, 1, '')
END
GOSELECT DISTINCT dbo.F_COMBINESTR(number) FROM t GROUP BY numberDROP TABLE t
DROP FUNCTION F_COMBINESTR
/*
A+B
A+B+C
B+C
C+D
*/
--这一句就够了
select distinct content = stuff((select '+'+content from t where number = a.number order by content for xml path('')),1,1,'')
from t a
create table tc
(
number int,
[content] nvarchar(10)
)
GO
insert into tc values(1,'A')
insert into tc values(1,'B')
insert into tc values(1,'C')
insert into tc values(2,'A')
insert into tc values(2,'B')
insert into tc values(3,'B')
insert into tc values(3,'C')
insert into tc values(4,'C')
insert into tc values(4,'D')
insert into tc values(5,'D')
insert into tc values(5,'C')
insert into tc values(6,'C')
insert into tc values(6,'B')
GO
select * into #tc from tc
declare @number int,
@content nvarchar(10)
update #tc
set
@content=case when @number=number then @content +'+'+content else content end,
@number=number,
content=@content
select * from #tcselect distinct number, max(content) as content from #tc a where
exists(select 1 from #tc where content<>a.content and number >a.number) group by number
create table t
(
number int,
[content] nvarchar(10)
)
GO
insert into t values(1,'A')
insert into t values(1,'B')
insert into t values(1,'C')
insert into t values(2,'A')
insert into t values(2,'B')
insert into t values(3,'B')
insert into t values(3,'C')
insert into t values(4,'C')
insert into t values(4,'D')
insert into t values(5,'D')
insert into t values(5,'C')
insert into t values(6,'C')
insert into t values(6,'B')
GO CREATE FUNCTION F_COMBINESTR(@ID int)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @SQL VARCHAR(100)
SET @SQL=''
SELECT @SQL=@SQL+'+' + [content]
FROM (SELECT DISTINCT [content] FROM t WHERE number=@ID) T
ORDER BY [content]
RETURN STUFF(@SQL, 1, 1, '')
END
GOSELECT DISTINCT dbo.F_COMBINESTR(number) FROM t GROUP BY numberDROP TABLE t
DROP FUNCTION F_COMBINESTR
/*
A+B
A+B+C
B+C
C+D
*/
stuff
(
number int,
[content] nvarchar(10)
)
GO
insert into t values(1,'A')
insert into t values(1,'B')
insert into t values(1,'C')
insert into t values(2,'A')
insert into t values(2,'B')
insert into t values(3,'B')
insert into t values(3,'C')
insert into t values(4,'C')
insert into t values(4,'D')
insert into t values(5,'D')
insert into t values(5,'C')
insert into t values(6,'C')
insert into t values(6,'B')
GO
select distinct content = stuff((select distinct '+'+ content from t where number = a.number for xml path('')),1,1,'')
from t a
/*
content
-----------
A+B
A+B+C
B+C
C+D(4 行受影响)
*/
FROM t
GROUP BY number
GO
(
number int,
[content] nvarchar(10)
) insert into @t
values(1,'A'),
(1,'B'),
(1,'C'),
(2,'A'),
(2,'B') ,
(3,'B') ,
(3,'C') ,
(4,'C') ,
(4,'D') ,
(5,'D') ,
(5,'C') ,
(6,'C') ,
(6,'B') SELECT number,
STUFF((SELECT '+' + [content] AS [text()]
FROM @t AS t2
WHERE t2.number = t1.number
ORDER BY number
FOR XML PATH('')), 1, 1, '') AS string
FROM @t AS t1
GROUP BY number;
GO