declare @t table(id int, c1 varchar(10), c2 int) insert @t select 1, 'I' , 1 union all select 1, 'have' , 2 union all select 1, 'a' , 3 union all select 1, 'dream', 4 union all select 2, 'oh' , 1 union all select 2, 'my' , 2 union all select 2, 'god', 3 declare @id int declare @s varchar(1000) set @s = '' select @s = isnull(@s+' ','') + case when @id=id then '' else ' ' end + c1 ,@id = id from @t order by id,c2print @s--结果 I have a dream oh my god
换下插入次序,结果一样 declare @t table(id int, c1 varchar(10), c2 int) insert @t select 1, 'I' , 1 union all select 1, 'have' , 2 union all select 2, 'my' , 2 union all select 2, 'god', 3 union all select 1, 'a' , 3 union all select 1, 'dream', 4 union all select 2, 'oh' , 1 declare @id int declare @s varchar(1000) set @s = '' select @s = isnull(@s+' ','') + case when @id=id then '' else ' ' end + c1 ,@id = id from @t order by id,c2print @s--结果 I have a dream oh my god
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB IF OBJECT_ID('MU_TEST') IS NOT NULL DROP FUNCTION MU_TEST GO CREATE TABLE TB( ID INT, C1 VARCHAR(100), C2 INT ) INSERT INTO TB SELECT 1, 'I', 1 UNION ALL SELECT 1, 'have', 2 UNION ALL SELECT 1, 'a', 3 UNION ALL SELECT 1, 'dream', 4 UNION ALL SELECT 2, 'oh', 1 UNION ALL SELECT 2, 'my', 2 UNION ALL SELECT 2, 'god', 3 GO CREATE FUNCTION MU_TEST( @ID INT ) RETURNS VARCHAR(8000) AS BEGIN DECLARE @STR VARCHAR(8000) SELECT @STR=ISNULL(@STR+' ','')+C1 FROM TB WHERE ID=@ID ORDER BY C2 ASC RETURN @STR END GO SELECT DISTINCT ID,DBO.MU_TEST(ID) FROM TB /* 1 I have a dream 2 oh my god */
declare @t table(id int, c1 varchar(10), c2 int) insert @t select 1, 'I' , 1 union all select 1, 'have' , 2 union all select 1, 'a' , 3 union all select 1, 'dream', 4 union all select 2, 'oh' , 1 union all select 2, 'my' , 2 union all select 2, 'god', 3 declare @id int declare @s varchar(1000) set @s = '' select @s = isnull(@s+' ','') + case when @id=id then '' else ' ' end + c1 ,@id = id from @t order by id,c2print @s
declare @t table(id int, c1 varchar(10), c2 int) insert @t select 1, 'I' , 1 union all select 1, 'have' , 2 union all select 2, 'my' , 2 union all select 2, 'god', 3 union all select 1, 'a' , 3 union all select 1, 'dream', 4 union all select 2, 'oh' , 1 declare @id int declare @s varchar(1000) set @s = '' select @s = isnull(@s+' ','') + case when @id=id then '' else ' ' end + c1 ,@id = id from @t order by id,c2print @s--结果 I have a dream oh my god
declare @t table(id int, c1 varchar(10), c2 int) insert @t select 1, 'I' , 1 union all select 1, 'have' , 2 union all select 2, 'my' , 2 union all select 2, 'god', 3 union all select 1, 'a' , 3 union all select 1, 'dream', 4 union all select 2, 'oh' , 1 declare @id int declare @s varchar(1000) set @s = '' select @s = isnull(@s+' ','') + case when @id=id then '' else char(10) end + c1 ,@id = id from @t order by id,c2print @s
insert @t select
1, 'I' , 1
union all select
1, 'have' , 2
union all select
1, 'a' , 3
union all select
1, 'dream', 4
union all select
2, 'oh' , 1
union all select
2, 'my' , 2
union all select
2, 'god', 3 declare @id int
declare @s varchar(1000)
set @s = ''
select @s = isnull(@s+' ','') + case when @id=id then '' else '
' end + c1
,@id = id
from @t
order by id,c2print @s--结果
I have a dream
oh my god
insert @t select
1, 'I' , 1
union all select
1, 'have' , 2
union all select
2, 'my' , 2
union all select
2, 'god', 3
union all select
1, 'a' , 3
union all select
1, 'dream', 4
union all select
2, 'oh' , 1 declare @id int
declare @s varchar(1000)
set @s = ''
select @s = isnull(@s+' ','') + case when @id=id then '' else '
' end + c1
,@id = id
from @t
order by id,c2print @s--结果
I have a dream
oh my god
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
IF OBJECT_ID('MU_TEST') IS NOT NULL DROP FUNCTION MU_TEST
GO
CREATE TABLE TB(
ID INT,
C1 VARCHAR(100),
C2 INT
)
INSERT INTO TB
SELECT 1, 'I', 1 UNION ALL
SELECT 1, 'have', 2 UNION ALL
SELECT 1, 'a', 3 UNION ALL
SELECT 1, 'dream', 4 UNION ALL
SELECT 2, 'oh', 1 UNION ALL
SELECT 2, 'my', 2 UNION ALL
SELECT 2, 'god', 3
GO
CREATE FUNCTION MU_TEST(
@ID INT
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @STR VARCHAR(8000)
SELECT @STR=ISNULL(@STR+' ','')+C1 FROM TB
WHERE ID=@ID
ORDER BY C2 ASC
RETURN @STR
END
GO
SELECT DISTINCT ID,DBO.MU_TEST(ID) FROM TB
/*
1 I have a dream
2 oh my god
*/
insert @t select
1, 'I' , 1
union all select
1, 'have' , 2
union all select
1, 'a' , 3
union all select
1, 'dream', 4
union all select
2, 'oh' , 1
union all select
2, 'my' , 2
union all select
2, 'god', 3 declare @id int
declare @s varchar(1000)
set @s = ''
select @s = isnull(@s+' ','') + case when @id=id then '' else '
' end + c1
,@id = id
from @t
order by id,c2print @s
declare @t table(id int, c1 varchar(10), c2 int)
insert @t select
1, 'I' , 1
union all select
1, 'have' , 2
union all select
2, 'my' , 2
union all select
2, 'god', 3
union all select
1, 'a' , 3
union all select
1, 'dream', 4
union all select
2, 'oh' , 1 declare @id int
declare @s varchar(1000)
set @s = ''
select @s = isnull(@s+' ','') + case when @id=id then '' else '
' end + c1
,@id = id
from @t
order by id,c2print @s--结果
I have a dream
oh my god
insert @t select
1, 'I' , 1
union all select
1, 'have' , 2
union all select
2, 'my' , 2
union all select
2, 'god', 3
union all select
1, 'a' , 3
union all select
1, 'dream', 4
union all select
2, 'oh' , 1 declare @id int
declare @s varchar(1000)
set @s = ''
select @s = isnull(@s+' ','') +
case when @id=id then '' else char(10) end + c1 ,@id = id
from @t order by id,c2print @s