关于sql字段拆分

satanail 2009-10-15 09:15:53

小弟不才，请各位高手指教
table a
name xm code
张三 aaa 1
张三 bbb 2
张三 ccc 3
李四 aaa 1
李四 ddd 4
结果如下

name xm xm2
张三 aaa bbb+ccc
李四 aaa ddd

xm和xm2都是字符串
这样的结果该如何去做？

...全文

83 6 打赏收藏转发到动态举报

写回复

用AI写文章

6 条回复

切换为时间正序

请发表友善的回复…

发表回复

satanail 2009-10-15

打赏
举报

谢谢各位高手指教，小弟谢过~

SQL77 2009-10-15

打赏
举报

http://topic.csdn.net/u/20091014/21/a4343a3f-fed7-4c1a-b485-5f16430b5d40.html

昨天不是有类似的

chuifengde 2009-10-15

打赏
举报

CREATE TABLE test24(NAME VARCHAR(20),  xm VARCHAR(20),code INT)

INSERT INTO test24 SELECT '张三','aaa',    1 

union all select '张三'  ,'bbb',    2 

union all select '张三'  ,'ccc',    3 

union all select '李四'  ,'aaa',    1 

union all select '李四'  ,'ddd',    4 



go



create  FUNCTION getTest24(@name VARCHAR(20),@name1 VARCHAR(20))

RETURNS VARCHAR(200)

BEGIN

	DECLARE @sql VARCHAR(1000)

	SELECT @sql=ISNULL(@sql+'+','') +xm FROM test24 WHERE NAME=@name AND xm<>@name1

	RETURN @sql

END



GO 

SELECT name,min(xm) xm,dbo.gettest24(name,min(xm)) xm2 FROM test24 t GROUP BY NAME



--result

/*name                 xm                   xm2                            

-------------------- -------------------- ------------------------------ 

李四                   aaa                  ddd

张三                   aaa                  bbb+ccc



（所影响的行数为 2 行）



*/

--小F-- 2009-10-15

打赏
举报

----------------------------------------------------------------

-- Author  :fredrickhu(我是小F，向高手学习)

-- Date    :2009-10-15 09:18:10

-- Version:

--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 

--	Nov 24 2008 13:01:59 

--	Copyright (c) 1988-2005 Microsoft Corporation

--	Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

--

----------------------------------------------------------------

--> 测试数据：[tb]

if object_id('[tb]') is not null drop table [tb]

go 

create table [tb]([name] varchar(4),[xm] varchar(3),[code] int)

insert [tb]

select '张三','aaa',1 union all

select '张三','bbb',2 union all

select '张三','ccc',3 union all

select '李四','aaa',1 union all

select '李四','ddd',4

--------------开始查询--------------------------

select 

  name,

  max(case code when  1 then ltrim(xm)  end) as xm,

  xm2=stuff((select ','+ltrim([xm]) from tb t where name=tb.name and code!=1 for xml path('')), 1, 1, '')

from 

  tb

group by

  name 



----------------结果----------------------------

/*name xm   xm2

---- ---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

李四   aaa  ddd

张三   aaa  bbb,ccc

警告: 聚合或其他 SET 操作消除了空值。



(2 行受影响)

 

*/

liangCK 2009-10-15

打赏
举报

-------------------------------------

--  Author : liangCK 梁爱兰

--  Comment: 小梁 爱 兰儿

--  Date   : 2009-10-15 09:17:53

-------------------------------------

 

--> 生成测试数据: @tb

DECLARE @tb TABLE (name varchar(4),xm varchar(3),code int)

INSERT INTO @tb

SELECT '张三','aaa',1 UNION ALL

SELECT '张三','bbb',2 UNION ALL

SELECT '张三','ccc',3 UNION ALL

SELECT '李四','aaa',1 UNION ALL

SELECT '李四','ddd',4



--SQL查询如下:



SELECT A.name,A.xm,

    STUFF(B.doc,1,1,'') AS xm2

FROM (SELECT name,MAX(CASE WHEN code = 1 THEN xm ELSE '' END) AS xm

      FROM @tb GROUP BY name) AS A

  CROSS APPLY (SELECT doc = (SELECT ','+RTRIM(xm) AS [text()]

                             FROM @tb

                             WHERE name = A.name AND code > 1

                             FOR XML PATH(''),TYPE).value('.','varchar(50)') ) AS B



/*

name xm   xm2

---- ---- --------------------------------------------------

李四   aaa  ddd

张三   aaa  bbb,ccc



(2 行受影响)

*/