例如一张表中有字段3个:
date string1 string 2 三个字段
2004-02-01 ab qq
2004-02-01 ab cc
2004-03-03 cd bb
2004-02-01 ef qq
2004-02-01 ef cc
如何把相同date下的string1字段下不同的值相加成一个新字段,string2的值不用考虑或做判断:
date string1 string 2
2004-02-01 ab,ef qq
2004-02-01 ab,ef cc
2004-03-03 cd bb
判断就是date字段值相同,string1值不同才合并,不要用string2来判断。
有高手没?
date string1 string 2 三个字段
2004-02-01 ab qq
2004-02-01 ab cc
2004-03-03 cd bb
2004-02-01 ef qq
2004-02-01 ef cc
如何把相同date下的string1字段下不同的值相加成一个新字段,string2的值不用考虑或做判断:
date string1 string 2
2004-02-01 ab,ef qq
2004-02-01 ab,ef cc
2004-03-03 cd bb
判断就是date字段值相同,string1值不同才合并,不要用string2来判断。
有高手没?
有高手没?上面还说错了,如果只用date判断,结果将是如下两种中的一种:
date string1 string2
2004-02-01 ab,ab,ef,ef qq
2004-03-03 cd bb date string1 string2
2004-02-01 ab,ab,ef,ef cc
2004-03-03 cd bb和你最初的需求不符合.
(
select distinct [date],string1 from tb
),
with cte2 as
(
select [date],STUFF((select ','+string1 from cte where a.[date]=[date] order by string1 for xml path('')),1,1,'')
from cte a
)
select b.*,a.string2
from cte2 b join tb a on b.date=a.date
date string1 string 2
2004-02-01 ab,ef qq
2004-03-03 cd bb或:
date string1 string 2
2004-02-01 ab,ef cc
2004-03-03 cd bb然后再对上面进行union,这样行不行?
-- Author : htl258(Tony)
-- Date : 2010-04-23 10:58:57
-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
--------------------------------------------------------------------------
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([date] DATETIME,[string1] NVARCHAR(10),[string2] NVARCHAR(10))
INSERT [tb]
SELECT N'2004-02-01','ab','qq' UNION ALL
SELECT N'2004-02-01','ab','cc' UNION ALL
SELECT N'2004-03-03','cd','bb' UNION ALL
SELECT N'2004-02-01','ef','qq' UNION ALL
SELECT N'2004-02-01','ef','cc'
GO
--SELECT * FROM [tb]-->SQL查询如下:
select date,
[string1]=stuff((select ','+[string1] from tb where date=t.date and [string2]=t.[string2] for xml path('')),1,1,''),
[string2]
from tb t
group by date,[string2]
/*
date string1 string2
2004-02-01 00:00:00.000 ab,ef cc
2004-02-01 00:00:00.000 ab,ef qq
2004-03-03 00:00:00.000 cd bb
*/这样不行吗
create table tb(date datetime , string1 varchar(10),string2 varchar(10))
insert into tb values('2004-02-01', 'ab', 'qq')
insert into tb values('2004-02-01', 'ab', 'cc')
insert into tb values('2004-03-03', 'cd', 'bb')
insert into tb values('2004-02-01', 'ef', 'qq')
insert into tb values('2004-02-01', 'ef', 'cc')
gocreate function dbo.f_str(@date datetime) returns varchar(100)
as
begin
declare @str varchar(1000)
set @str = ''
select @str = @str + ',' + cast(string1 as varchar) from (select distinct date , string1 from tb) t where date = @date
set @str = right(@str , len(@str) - 1)
return @str
end
go--调用函数
select m.* , n.string2 from
(select date , string1 = dbo.f_str(date) from (select distinct date , string1 from tb) t group by date) m
cross join
(select distinct date , string2 from tb) n
where m.date = n.datedrop function dbo.f_str
drop table tb/*
date string1 string2
------------------------------------------------------ ---------------------------------------------------------------------------------------------------- ----------
2004-02-01 00:00:00.000 ab,ef cc
2004-02-01 00:00:00.000 ab,ef qq
2004-03-03 00:00:00.000 cd bb(所影响的行数为 3 行)*/
create table tb(date datetime , string1 varchar(10),string2 varchar(10))
insert into tb values('2004-02-01', 'ab', 'qq')
insert into tb values('2004-02-01', 'ab', 'cc')
insert into tb values('2004-03-03', 'cd', 'bb')
insert into tb values('2004-02-01', 'ef', 'qq')
insert into tb values('2004-02-01', 'ef', 'cc')
go
select m.* , n.string2 from
(
select date, [string1] = stuff((select ',' + [string1] from (select distinct date , string1 from tb) t where date = m.date for xml path('')) , 1 , 1 , '')
from (select distinct date , string1 from tb) m
group by date
) m
cross join
(select distinct date , string2 from tb) n
where m.date = n.datedrop table tb/*
date string1 string2
----------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------
2004-02-01 00:00:00.000 ab,ef cc
2004-02-01 00:00:00.000 ab,ef qq
2004-03-03 00:00:00.000 cd bb(3 行受影响)*/
如果
string1的值为aa,bb,aa 那就那几行的值还是变为aa,bb,跟本不用考虑string2的值。
create table #tb3(date char(10),string1 varchar(10),string2 varchar(10))
insert #tb3 select '2004-02-01','ab','qq'
insert #tb3 select '2004-02-01','ab','cc'
insert #tb3 select '2004-03-03','cd','bb'
insert #tb3 select '2004-02-01','ef','qq'
insert #tb3 select '2004-02-01','ef','cc'
with cte as(
select *,row_number() over(partition by date,string1 order by string1) as rn from #tb3
)
select date,rn
,stuff((select ' '+string1 from cte where a.date=date and a.rn=rn order by string1 for xml path('')),1,1,'')
from cte a
group by date,rndate rn
---------- -------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2004-02-01 1 ab ef
2004-02-01 2 ab ef
2004-03-03 1 cd(3 行受影响)
userid date string1 string2 string3 string4
001 2004-03-01 aa k1
001 2004-03-01 aa k2
001 2004-03-01 aa
001 2004-03-01 aa q4 k3
001 2004-03-01 ee f1
001 2004-03-01 dd k1
001 2004-03-01 dd k2
001 2004-03-01 dd k3
判断date 是否相同,相同情况下string1 值不同的合并,得到
userid date string1 string2 string3 string4
001 2004-03-01 aa,dd k1
001 2004-03-01 aa,dd k2
001 2004-03-01 aa,dd
001 2004-03-01 aa q4 k3
001 2004-03-01 ee f1