表
name type
sun A
cat B
dog B
mon A
fri A
red C
如何得到一个字符串
A:sun;mon;fri, B:cat;dog, C:red
------解决方案--------------------
- SQL code
--> 测试数据：[test]if object_id('[test]') is not null drop table [test]create table [test]([name] varchar(3),[type] varchar(1))insert [test]select 'sun','A' union allselect 'cat','B' union allselect 'dog','B' union allselect 'mon','A' union allselect 'fri','A' union allselect 'red','C'declare @str varchar(50)set @str='';with tas(SELECT *FROM (SELECT DISTINCT [type] FROM [test])AOUTER APPLY( SELECT [name]= STUFF(REPLACE(REPLACE( ( SELECT [name] FROM [test] N WHERE [type] = A.[type] FOR XML AUTO ), '<N name="', ','), '"/>', ''), 1, 1, ''))N)select @[email protected]+';'+[type]+':'+name from tselect right(@str,len(@str)-1) as value/*value----------------------------------------A:sun,mon,fri;B:cat,dog;C:red*/
------解决方案--------------------
- SQL code
--> 测试数据：[test]if object_id('[test]') is not null drop table [test]gocreate table [test]([name] varchar(3),[type] varchar(1))goinsert [test]select 'sun','A' union allselect 'cat','B' union allselect 'dog','B' union allselect 'mon','A' union allselect 'fri','A' union allselect 'red','C'godeclare @sql varchar(max)with cte as( select [type], rusult=stuff ( ( select ';'+name from TEST a where a.type=test.type for xml path('') ) ,1,1,'' ) from TEST group by [type])select @sql=isnull(@sql,'')+[type]+':'+rusult+' ' from cteprint @sql--A:sun;mon;fri B:cat;dog C:red
------解决方案--------------------
- SQL code
表a name num aa 1 bb 2 cc 4 dd 4 ee 2 一条语句实现如下:name aa,bb,cc,dd,ee DECLARE @STR VARCHAR(8000)SELECT @STR=ISNULL(@STR+',','')+name FROM (SELECT DISTINCT NAME FROM A)AS TSELECT @STR问题描述：无论是在sql 2000，还是在sql 2005 中,都没有提供字符串的聚合函数， 所以，当我们在处理下列要求时，会比较麻烦：有表tb, 如下：id value1 aa1 bb2 aaa2 bbb2 ccc需要得到结果：id values1 aa,bb2 aaa,bbb,ccc即，group by id, 求value 的和（字符串相加）1. 旧的解决方法-- 1. 创建处理函数CREATE FUNCTION dbo.f_str(@id int)RETURNS varchar(8000)ASBEGIN DECLARE @r varchar(8000) SET @r = '' SELECT @r = @r + ',' + value FROM tb WHERE [email protected] RETURN STUFF(@r, 1, 1, '')ENDGO-- 调用函数SELECt id, values=dbo.f_str(id) FROM tb GROUP BY id-- 2. 新的解决方法-- 示例数据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)AOUTER 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,bb2 aaa,bbb,ccc(2 行受影响)--*/--各种字符串分函数--3.3.1 使用游标法进行字符串合并处理的示例。--处理的数据CREATE TABLE tb(col1 varchar(10),col2 int)INSERT tb SELECT 'a',1UNION ALL SELECT 'a',2UNION ALL SELECT 'b',1UNION ALL SELECT 'b',2UNION ALL SELECT 'b',3--合并处理--定义结果集表变量DECLARE @t TABLE(col1 varchar(10),col2 varchar(100))--定义游标并进行合并处理DECLARE tb CURSOR LOCALFORSELECT col1,col2 FROM tb ORDER BY col1,col2DECLARE @col1_old varchar(10),@col1 varchar(10),@col2 int,@s varchar(100)OPEN tbFETCH tb INTO @col1,@col2SELECT @[email protected],@s=''WHILE @@FETCH_STATUS=0BEGIN IF @[email protected]_old SELECT @[email protected]+','+CAST(@col2 as varchar) ELSE BEGIN INSERT @t VALUES(@col1_old,STUFF(@s,1,1,'')) SELECT @s=','+CAST(@col2 as varchar),@[email protected] END FETCH tb INTO @col1,@col2ENDINSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))CLOSE tbDEALLOCATE tb--显示结果并删除测试数据SELECT * FROM @tDROP TABLE tb/*--结果col1 col2a 1,2b 1,2,3--*/GO--3.3.2 使用用户定义函数，配合SELECT处理完成字符串合并处理的示例--处理的数据CREATE TABLE tb(col1 varchar(10),col2 int)INSERT tb SELECT 'a',1UNION ALL SELECT 'a',2UNION ALL SELECT 'b',1UNION ALL SELECT 'b',2UNION ALL SELECT 'b',3GO--合并处理函数CREATE FUNCTION dbo.f_str(@col1 varchar(10))RETURNS varchar(100)ASBEGIN DECLARE @re varchar(100) SET @re='' SELECT @[email protected]+','+CAST(col2 as varchar) FROM tb WHERE [email protected] RETURN(STUFF(@re,1,1,''))ENDGO--调用函数SELECT col1,col2=dbo.f_str(col1) FROM tb GROUP BY col1--删除测试DROP TABLE tbDROP FUNCTION f_str/*--结果col1 col2a 1,2b 1,2,3--*/GO--3.3.3 使用临时表实现字符串合并处理的示例--处理的数据CREATE TABLE tb(col1 varchar(10),col2 int)INSERT tb SELECT 'a',1UNION ALL SELECT 'a',2UNION ALL SELECT 'b',1UNION ALL SELECT 'b',2UNION ALL SELECT 'b',3--合并处理SELECT col1,col2=CAST(col2 as varchar(100)) INTO #t FROM tbORDER BY col1,col2DECLARE @col1 varchar(10),@col2 varchar(100)UPDATE #t SET @col2=CASE WHEN @col1=col1 THEN @col2+','+col2 ELSE col2 END, @col1=col1, [email protected]SELECT * FROM #t/*--更新处理后的临时表col1 col2a 1a 1,2b 1b 1,2b 1,2,3--*/--得到最终结果SELECT col1,col2=MAX(col2) FROM #t GROUP BY col1/*--结果col1 col2a 1,2b 1,2,3--*/--删除测试DROP TABLE tb,#tGO--3.3.4.1 每组<=2 条记录的合并--处理的数据CREATE TABLE tb(col1 varchar(10),col2 int)INSERT tb SELECT 'a',1UNION ALL SELECT 'a',2UNION ALL SELECT 'b',1UNION ALL SELECT 'b',2UNION ALL SELECT 'c',3--合并处理SELECT col1, col2=CAST(MIN(col2) as varchar) +CASE WHEN COUNT(*)=1 THEN '' ELSE ','+CAST(MAX(col2) as varchar) ENDFROM tbGROUP BY col1DROP TABLE tb/*--结果col1 col2 ---------- ----------a 1,2b 1,2c 3--*/--3.3.4.2 每组<=3 条记录的合并--处理的数据CREATE TABLE tb(col1 varchar(10),col2 int)INSERT tb SELECT 'a',1UNION ALL SELECT 'a',2UNION ALL SELECT 'b',1UNION ALL SELECT 'b',2UNION ALL SELECT 'b',3UNION ALL SELECT 'c',3--合并处理SELECT col1, col2=CAST(MIN(col2) as varchar) +CASE WHEN COUNT(*)=3 THEN ',' +CAST((SELECT col2 FROM tb WHERE col1=a.col1 AND col2 NOT IN(MAX(a.col2),MIN(a.col2))) as varchar) ELSE '' END +CASE WHEN COUNT(*)>=2 THEN ','+CAST(MAX(col2) as varchar) ELSE '' ENDFROM tb aGROUP BY col1DROP TABLE tb/*--结果col1 col2---------- ------------a 1,2b 1,2,3c 3--*/GOif not object_id('A') is null drop table AGoCreate table A([id] int,[cname] nvarchar(2))Insert Aselect 1,N'张三' union allselect 2,N'李四' union allselect 3,N'王五' union allselect 4,N'蔡六'Go--> --> if not object_id('B') is null drop table BGoCreate table B([id] int,[cname] nvarchar(5))Insert Bselect 1,N'1,2,3' union allselect 2,N'3,4'Gocreate function F_str(@cname nvarchar(100))returns nvarchar(100)asbegin select @cname=replace(@cname,ID,[cname]) from A where patindex('%,'+rtrim(ID)+',%',',[email protected]+',')>0return @cnameendgoselect [id],dbo.F_str([cname])[cname] from Bid cname1 张三,李四,王五2 王五,蔡六(2 個資料列受到影響)我现在有一字段值为:a,b,c 分隔符为逗号. 我现在想实现目地值为: 字段: id name 1 a 2 b 3 c 在SQL中咋实现呢?declare @str varchar(8000) set @str = 'a1,b1,c2,d1,e3,f5' set @str = 'select name='''+replace(@str,',',''''+' union all select ''')+'''' set @str='select id=identity(int,1,1),name into #temp from ([email protected]+') a select * from #temp drop table #temp'exec(@str)--参考:CREATE TABLE TB(ID VARCHAR(6), COLOR NVARCHAR(30))INSERT TBSELECT '173160', N'#特深蓝色,#特深蓝色' UNION ALL SELECT '173160', N'#特深蓝色,#特深蓝色' UNION ALL SELECT '911169', N'#宝蓝色,#宝蓝色,#花灰色,#花灰色' UNION ALL SELECT '911169', N'#宝蓝色,#宝蓝色,#花灰色,#花灰色' UNION ALL SELECT '911169', N'#宝蓝色,#宝蓝色,#花灰色,#花灰色' UNION ALL SELECT '911169', N'#宝蓝色,#宝蓝色,#花灰色,#花灰色'GOCREATE FUNCTION F_getStr(@color nvarchar(30))returns nvarchar(30)asbegin declare @str nvarchar(30),@temp nvarchar(30) set @str='' set @temp='' while charindex(',', @color+',')>0 begin set @temp=left(@color, charindex(',', @color+',')-1) if charindex(',[email protected]+',', ',[email protected]+',')=0 set @[email protected]+',[email protected] set @color=stuff(@color, 1, charindex(',', @color+','), '') end return stuff(@str, 1, 1, '')endgoSELECT ID,dbo.F_getStr(COLOR) as COLOR FROM TBDROP TABLE TBDROP FUNCTION F_getStr/*ID COLOR173160 #特深蓝色173160 #特深蓝色911169 #宝蓝色,#花灰色911169 #宝蓝色,#花灰色911169 #宝蓝色,#花灰色911169 #宝蓝色,#花灰色*/