求一条SQL语句

Question

先有一个表TB如下：name  age sex
a     18   1
b     20   2
c     18   1
d     20   1
e     18   2
f     20   2现在需要查出的结果是
age  names
18   a,c,e
20   b,d,f请问用语句怎么实现？

Answer 1

问题描述：
无论是在sql 2000，还是在 sql 2005 中,都没有提供字符串的聚合函数，
  所以，当我们在处理下列要求时，会比较麻烦：
有表tb, 如下：
id    value
----- ------
1     aa
1     bb
2     aaa
2     bbb
2     ccc
需要得到结果：
id     values
------ -----------
1      aa,bb
2      aaa,bbb,ccc
即， group by id, 求 value 的和（字符串相加）1. 旧的解决方法-- 1. 创建处理函数
CREATE FUNCTION dbo.f_str(@id int)
RETURNS varchar(8000)
AS
BEGIN
    DECLARE @r varchar(8000)
    SET @r = ''
    SELECT @r = @r + ',' + value
    FROM tb
    WHERE id=@id
    RETURN STUFF(@r, 1, 1, '')
END
GO
-- 调用函数SELECt id, values=dbo.f_str(id) 
FROM tb 
GROUP BY id-- 2. 新的解决方法(适用于2005及以后版本)
-- 示例数据
DECLARE @t TABLE(id int, value varchar(10))
INSERT @t SELECT 1, 'aa'
UNION ALL SELECT 1, 'bb'
UNION ALL SELECT 2, 'aaa'
UNION ALL SELECT 2, 'bbb'
UNION ALL SELECT 2, 'ccc'-- 查询处理
SELECT *
FROM(
    SELECT DISTINCT 
        id
    FROM @t
)A
OUTER APPLY(
    SELECT 
        [values]= STUFF(REPLACE(REPLACE(
            (
                SELECT value FROM @t N
                WHERE id = A.id
                FOR XML AUTO
            ), '<N value="', ','), '"/>', ''), 1, 1, '')
)N/*--结果
id          values
----------- ----------------
1           aa,bb
2           aaa,bbb,ccc
(2 行受影响)
--*/CSDN 社区帖子地址 附: 合并与分拆的CLR， sql2005的示例中有：
在安装sql 2005的示例后，默认安装目录为
drive:\Program Files\Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\StringUtilities中

Answer 2

http://topic.csdn.net/u/20080612/22/c850499f-bce3-4877-82d5-af2357857872.html

Answer 3

2005--> 生成测试数据: @T
DECLARE @T TABLE (name VARCHAR(1),age INT,sex INT)
INSERT INTO @T
SELECT 'a',18,1 UNION ALL
SELECT 'b',20,2 UNION ALL
SELECT 'c',18,1 UNION ALL
SELECT 'd',20,1 UNION ALL
SELECT 'e',18,2 UNION ALL
SELECT 'f',20,2--SQL查询如下:SELECT
    A.age,
    name=B.name.value('(/Row)[1]','varchar(50)')
FROM (
    SELECT DISTINCT
        age
    FROM @T
) AS A 
    CROSS APPLY
    (
        SELECT 
           (
               SELECT 
                   name
               FROM @T
               WHERE age=A.age
               ORDER BY sex,name
               FOR XML PATH('Row'),TYPE
           ).query('
              <Row>
              {
               for $name in /Row[position()<last()]
                   return concat(string($name/name[1]),",")
              }
              {string((/Row[last()]/name)[1])}
              </Row>
           ') AS name
    ) AS B/*
age         name
----------- --------------------------------------------------
18          a, c,e
20          d, b,f(2 行受影响)*/

Answer 4

/*
name  age sex 
a    18  1 
b    20  2 
c    18  1 
d    20  1 
e    18  2 
f    20  2 现在需要查出的结果是 
age  names 
18  a,c,e 
20  b,d,f 
*/create table RR
(
  [name] varchar(20),
  age int,
  sex int
)
insert into RR select 'a',18,1
insert into RR select 'b',20,2
insert into RR select 'c',18,1
insert into RR select 'd',20,1
insert into RR select 'e',18,2
insert into RR select 'f',20,2create function dbo.FC_Str1(@age int)
returns varchar(100)
as
begin
   declare @i varchar(100)
   set @i=''
   select @i=@i+','+[name] from RR where age=@age
   return stuff(@i,1,1,'')
    
endselect age,dbo.FC_Str1(age) from RR group by age

Answer 5

create table t(name varchar(1),age int,sex int)
insert t(name,age,sex)
select 'a',18,1 union all
select 'b',20,2 union all
select 'c',18,1 union all
select 'd',20,1 union all
select 'e',18,2 union all
select 'f',20,2 create function str_fun(@age nvarchar(40))
returns nvarchar(100)
begin
 declare @str nvarchar(200)
 select @str=isnull(@str+',','') + name from t where age=@age
 return @str
endselect age,dbo.str_fun(age)as names from t group by age

Answer 6

DECLARE @t TABLE(id int, value varchar(10))
INSERT @t SELECT 1, 'aa'
UNION ALL SELECT 1, 'bb'
UNION ALL SELECT 2, 'aaa'
UNION ALL SELECT 2, 'bbb'
UNION ALL SELECT 2, 'ccc'-- 查询处理
SELECT *
FROM(
    SELECT DISTINCT 
        id
    FROM @t
)A
OUTER APPLY(
    SELECT 
        [values]= STUFF(REPLACE(REPLACE(
            (
                SELECT value FROM @t N
                WHERE id = A.id
                FOR XML AUTO
            ), '<N value="', ','), '"/>', ''), 1, 1, '')
)N

Answer 7

------------------------------------
-- Author:  happyflystone  
-- Date:2008-12-27 22:53:15
-------------------------------------- Test Data: TA
IF OBJECT_ID('TA') IS NOT NULL 
    DROP TABLE TA
Go
CREATE TABLE TA(name NVARCHAR(1),age INT,sex INT)
Go
INSERT INTO TA
 SELECT 'a',18,1 UNION ALL
 SELECT 'b',20,2 UNION ALL
 SELECT 'c',18,1 UNION ALL
 SELECT 'd',20,1 UNION ALL
 SELECT 'e',18,2 UNION ALL
 SELECT 'f',20,2 
GO
--Start
SELECT 
age,[name]=replace(replace(replace((select [name] 
                        from ta 
                        where age = a.age for xml auto),
                       '"/><ta name="',','),'<ta name="',''),'"/>','')
FROM 
TA a
group by age
--Result:
/*age         name
----------- ----------------------------------------
18          a,c,e
20          b,d,f(2 行受影响)
*/
--End 

Answer 8

SQL2005
INSERT INTO tb
SELECT 'a',18,1 UNION ALL
SELECT 'b',20,2 UNION ALL
SELECT 'c',18,1 UNION ALL
SELECT 'd',20,1 UNION ALL
SELECT 'e',18,2 UNION ALL
SELECT 'f',20,2
select * from tbselect age,names = stuff((select ','+ name from tb where age = a.age for xml path('')),1,1,'' )from tb a group by age
age         names
----------- ----------------------------------------------------
18          a,c,e
20          b,d,f(2 row(s) affected)

Answer 9

SQL2005create table Tb(name nvarchar(1),age int,sex int)
INSERT INTO tb
SELECT 'a',18,1 UNION ALL
SELECT 'b',20,2 UNION ALL
SELECT 'c',18,1 UNION ALL
SELECT 'd',20,1 UNION ALL
SELECT 'e',18,2 UNION ALL
SELECT 'f',20,2
select * from tbselect age,names = stuff((select ','+ name from tb where age = a.age for xml path('')),1,1,'' )from tb a group by age
age         names
----------- ---------------------
18          a,c,e
20          b,d,f(2 row(s) affected)

Answer 10

SQL code
create table Tb(name nvarchar(1),age int,sex int)
INSERT INTO tb
SELECT 'a',18,1 UNION ALL
SELECT 'b',20,2 UNION ALL
SELECT 'c',18,1 UNION ALL
SELECT 'd',20,1 UNION ALL
SELECT 'e',18,2 UNION ALL
SELECT 'f',20,2
select * from tbselect age,names = stuff((select ','+ name from tb where age = a.age for xml path('')),1,1,'' )from tb a group by age
age        names 
----------- --------------------- 
18          a,c,e 
20          b,d,f