SELECT * FROM( SELECT DISTINCT id FROM TB )A OUTER APPLY( SELECT [lx]= STUFF(REPLACE(REPLACE( ( SELECT lx FROM TB N WHERE id = A.id FOR XML AUTO ), '<N lx="', ','), '"/>', ''), 1, 1, '') )N
create table tb (a varchar(10),b varchar(10)) insert tb select '2009', 'AA' union all select '2009', 'BB' union all select '2008', 'CC' union all select '2007', 'XX' union all select '2009', 'HH' union all select '2008', 'DD' union all select '2007', 'SS' union all select '2006', 'GG' go select * from tb CREATE FUNCTION dbo.f_str(@col1 varchar(10)) RETURNS varchar(100) AS BEGIN DECLARE @re varchar(100) SET @re='' SELECT @re=@re+','+CAST(b as varchar) FROM tb WHERE a=@col1 RETURN(STUFF(@re,1,1,'')) END GOselect a,dbo.f_str(a)[b] from tb group by a a b ---------- ------------------- 2006 GG 2007 XX,SS 2008 CC,DD 2009 AA,BB,HH(所影响的行数为 4 行) 本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/lihan6415151528/archive/2009/08/10/4431237.aspx
--2000 CREATE FUNCTION dbo.f_str(@col1 varchar(10)) RETURNS varchar(100) AS BEGIN DECLARE @re varchar(100) SET @re='' SELECT @re=@re+','+CAST(lx as varchar) FROM tb WHERE id=@col1 RETURN(STUFF(@re,1,1,'')) END GOselect id,dbo.f_str(id) from tb group by id --2005 select id, [lx]=stuff((select ','+[lx] from tb t where id=tb.id for xml path('')), 1, 1, '') from tb group by id
SELECT *
FROM(
SELECT DISTINCT
id
FROM TB
)A
OUTER APPLY(
SELECT
[lx]= STUFF(REPLACE(REPLACE(
(
SELECT lx FROM TB N
WHERE id = A.id
FOR XML AUTO
), '<N lx="', ','), '"/>', ''), 1, 1, '')
)N
create table tb (a varchar(10),b varchar(10))
insert tb
select '2009', 'AA' union all
select '2009', 'BB' union all
select '2008', 'CC' union all
select '2007', 'XX' union all
select '2009', 'HH' union all
select '2008', 'DD' union all
select '2007', 'SS' union all
select '2006', 'GG'
go
select * from tb
CREATE FUNCTION dbo.f_str(@col1 varchar(10))
RETURNS varchar(100)
AS
BEGIN
DECLARE @re varchar(100)
SET @re=''
SELECT @re=@re+','+CAST(b as varchar)
FROM tb
WHERE a=@col1
RETURN(STUFF(@re,1,1,''))
END
GOselect a,dbo.f_str(a)[b] from tb group by a a b
---------- -------------------
2006 GG
2007 XX,SS
2008 CC,DD
2009 AA,BB,HH(所影响的行数为 4 行)
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/lihan6415151528/archive/2009/08/10/4431237.aspx
--2000
CREATE FUNCTION dbo.f_str(@col1 varchar(10))
RETURNS varchar(100)
AS
BEGIN
DECLARE @re varchar(100)
SET @re=''
SELECT @re=@re+','+CAST(lx as varchar)
FROM tb
WHERE id=@col1
RETURN(STUFF(@re,1,1,''))
END
GOselect id,dbo.f_str(id) from tb group by id --2005
select id, [lx]=stuff((select ','+[lx] from tb t where id=tb.id for xml path('')), 1, 1, '')
from tb
group by id