我一个表,表结构如下:
编号 内容
001 0123
001 01234
001 012345
002 0121
002 1245
002 7895
003 1245
004 0013
004 12434
004 12456
我想实现这样的数据表示方式:
编号 内容
001 0123,01234,012345
002 0121,1245,7895
003 1245
004 0013,12434,12456
编号 内容
001 0123
001 01234
001 012345
002 0121
002 1245
002 7895
003 1245
004 0013
004 12434
004 12456
我想实现这样的数据表示方式:
编号 内容
001 0123,01234,012345
002 0121,1245,7895
003 1245
004 0013,12434,12456
create table T
(
编号 nvarchar(10),
内容 nvarchar(10)
)insert T select '001', '0123'
insert T select '001', '01234'
insert T select '001', '012345'
insert T select '002', '0121'
insert T select '002', '1245'
insert T select '002', '7895'
insert T select '003', '1245'
insert T select '004', '0013'
insert T select '004', '12434'
insert T select '004', '12456'
create function dbo.fn(@编号 nvarchar(10))
returns nvarchar(200)
as
begin
declare @re nvarchar(200) set @re=''
select @re=@re+','+内容
from T
where 编号=@编号 return stuff(@re,1,1,'')
endselect distinct 编号,内容=dbo.fn(编号)
from T
create table tt(id int,a varchar(20));
insert into tt(id,a)
select 1,'a'
union all
select 2,'a'
union all
select 3,'a'
union all
select 4,'b'
union all
select 5,'b'
union all
select 6,'c'
union all
select 7,'c'
--游标方案
declare @t table(a varchar(200),b varchar(200));
declare @m varchar(200)
set @m=''
declare c cursor for select distinct a from tt
open c
fetch next from c into @m
while @@fetch_status=0
begin
declare @a varchar(max)
set @a=''
select @a=@a+','+cast(id as varchar) from tt where a=@m
insert into @t(a,b)
select @m,stuff(@a,1,1,'')
fetch next from c into @m
end
close c
deallocate c
select * from @tDECLARE @t1 TABLE(col1 varchar(10),col2 varchar(100))--定义游标并进行合并处理
DECLARE tb CURSOR LOCAL
FOR
SELECT a,id FROM tt ORDER BY id,a
DECLARE @col1_old varchar(10),@col1 varchar(10),@col2 int,@s varchar(100)
OPEN tb
FETCH tb INTO @col1,@col2
SELECT @col1_old=@col1,@s=''
WHILE @@FETCH_STATUS=0
BEGIN
IF @col1=@col1_old
SELECT @s=@s+','+CAST(@col2 as varchar)
ELSE
BEGIN
INSERT @t1 VALUES(@col1_old,STUFF(@s,1,1,''))
SELECT @s=','+CAST(@col2 as varchar),@col1_old=@col1
END
FETCH tb INTO @col1,@col2
END
INSERT @t1 VALUES(@col1_old,STUFF(@s,1,1,''))
CLOSE tb
DEALLOCATE tbSELECT * FROM @t1--自定义函数方案
create function f_t(@a varchar(200))
returns nvarchar(4000)
as
begin
declare @s nvarchar(4000)
set @s=''
select @s=@s+','+cast(id as varchar) from tt where a=@a
return (stuff(@s,1,1,''))
end
--调用
select a,dbo.f_t(a) as a from tt group by a
--XML方案
SELECT a,
STUFF((SELECT ',' + cast(id as varchar) AS [text()] --此处STUFF函数的目的是除去字符串首部的逗号
FROM dbo.tt AS G2
WHERE G2.a = G1.a
ORDER BY [id]
FOR XML PATH('')), 1, 1, '') AS string
FROM dbo.tt AS G1
GROUP BY a;
--rownum技术(需事先知道分组内的成员个数)
SELECT a,
MAX(CASE WHEN rn = 1 THEN cast(id as varchar) ELSE '' END)
+ MAX(CASE WHEN rn = 2 THEN ',' + cast(id as varchar) ELSE '' END)
+ MAX(CASE WHEN rn = 3 THEN ',' + cast(id as varchar) ELSE '' END)
+ MAX(CASE WHEN rn = 4 THEN ',' + cast(id as varchar) ELSE '' END) AS string
FROM (SELECT a, id,
(SELECT COUNT(*)
FROM dbo.tt AS B
WHERE B.a = A.a
AND B.id <= A.id) AS rn
FROM dbo.tt AS A) AS D
GROUP BY a;
---
truncate table tt;
drop table tt;
(
编号 nvarchar(10),
内容 nvarchar(10)
)insert tr select '001', '0123'
insert tr select '001', '01234'
insert tr select '001', '012345'
insert tr select '002', '0121'
insert tr select '002', '1245'
insert tr select '002', '7895'
insert tr select '003', '1245'
insert tr select '004', '0013'
insert tr select '004', '12434'
insert tr select '004', '12456' SELECT * FROM(SELECT DISTINCT 编号 FROM tr)A
OUTER APPLY(SELECT
[values]= STUFF(REPLACE(REPLACE((SELECT 内容 FROM tr N WHERE 编号 = A.编号 FOR XML AUTO)
,'<N 内容="',','),'"/>', ''),1, 1, ''))N--结果
--001 0123,01234,012345
--002 0121,1245,7895
--003 1245
--004 0013,12434,12456drop table tr
借用楼上的表,2楼中的例子
create table tr
(
编号 nvarchar(10),
内容 nvarchar(10)
)insert tr select '001', '0123'
insert tr select '001', '01234'
insert tr select '001', '012345'
insert tr select '002', '0121'
insert tr select '002', '1245'
insert tr select '002', '7895'
insert tr select '003', '1245'
insert tr select '004', '0013'
insert tr select '004', '12434'
insert tr select '004', '12456' SELECT 编号,
STUFF((SELECT ','+内容 AS [text()]
FROM dbo.tr AS G2
WHERE G2.编号 = G1.编号
ORDER BY 内容
FOR XML PATH('')), 1, 1, '') AS 内容
FROM dbo.tr AS G1
GROUP BY 编号; drop table tr