create table #tmp
(
fdate varchar(10),
fcus varchar(20),
qty decimal(18,4)
)
insert into #tmp
select '2011-10-01','kh001',100
insert into #tmp
select '2011-10-01','kh002',300
insert into #tmp
select '2011-10-02','kh001',300
insert into #tmp
select '2011-10-03','kh002',300 select * from #tmp
在不使用游标的方法下获得如下结果2011-10-01 400.0000 kh001:100;kh002;300
2011-10-02 300.0000 kh001:300;
2011-10-03 300.0000 kh002:300;drop table #tmp
解决方案 »
- pt1314917、you_tube、xys_777、xiaoliaoyun拿分啦
- 求一个sql语句,可以列出某个数据库中所有有自动增长列的表
- varchar转化成numeric出错
- 查询当天时间之前的信息
- 怎样将 sql server 6.5 的数据(.dat 格式)文件恢复到 sql server 7.0 或 sql server 2000 中?
- sqlServer2000的默认字符集是什么呢?
- Mysql跟Mssql语句方面差多少?
- 请教存贮过程和SQL语言的问题。
- 简单问题100分等你,SQl的小问题
- 关于外联接的问题
- c#如何调用存储过程返回的多个结果集给gridview绑定?
- sql 高手求解 苦恼了一天
(
fdate varchar(10),
fcus varchar(20),
qty decimal(18,4)
)
insert into #tmp
select '2011-10-01','kh001',100
insert into #tmp
select '2011-10-01','kh002',300
insert into #tmp
select '2011-10-02','kh001',300
insert into #tmp
select '2011-10-03','kh002',300
GO
SELECT fdate,
STUFF((SELECT ';'+fcus FROM #tmp WHERE fdate=a.fdate FOR XML PATH('')),1,1,'') AS fcus,SUM(qty) AS qty
FROM
#tmp AS a
GROUP BY fdate/*
fdate fcus qty
2011-10-01 kh001;kh002 400.0000
2011-10-02 kh001 300.0000
2011-10-03 kh002 300.0000*/
(
fdate varchar(10),
fcus varchar(20),
qty decimal(18,4)
)
insert into #tmp
select '2011-10-01','kh001',100
insert into #tmp
select '2011-10-01','kh002',300
insert into #tmp
select '2011-10-02','kh001',300
insert into #tmp
select '2011-10-03','kh002',300
GO
SELECT fdate,SUM(qty) AS qty,
STUFF((SELECT ';'+fcus+':'+RTRIM(CAST(qty AS INT) )FROM #tmp WHERE fdate=a.fdate FOR XML PATH('')),1,1,'') AS fcusFROM
#tmp AS a
GROUP BY fdate/*
fdate qty fcus
2011-10-01 400.0000 kh001:100;kh002:300
2011-10-02 300.0000 kh001:300
2011-10-03 300.0000 kh002:300*/
STUFF((SELECT ';'+fcus+':'+RTRIM(qty )FROM #tmp WHERE fdate=a.fdate FOR XML PATH('')),1,1,'') AS fcus
FROM
#tmp AS a
GROUP BY fdate
參它方法參照 --> --> (Roy)生成測試數據
if not object_id('Tab') is null
drop table Tab
Go
Create table Tab([Col1] int,[Col2] nvarchar(1))
Insert Tab
select 1,N'a' union all
select 1,N'b' union all
select 1,N'c' union all
select 2,N'd' union all
select 2,N'e' union all
select 3,N'f'
Go合并表:SQL2000用函数:go
if object_id('F_Str') is not null
drop function F_Str
go
create function F_Str(@Col1 int)
returns nvarchar(100)
as
begin
declare @S nvarchar(100)
select @S=isnull(@S+',','')+Col2 from Tab where Col1=@Col1
return @S
end
go
Select distinct Col1,Col2=dbo.F_Str(Col1) from TabgoSQL2005用XML:方法1:select
a.Col1,Col2=stuff(b.Col2.value('/R[1]','nvarchar(max)'),1,1,'')
from
(select distinct COl1 from Tab) a
Cross apply
(select COl2=(select N','+Col2 from Tab where Col1=a.COl1 For XML PATH(''), ROOT('R'), TYPE))b方法2:select
a.Col1,COl2=replace(b.Col2.value('/Tab[1]','nvarchar(max)'),char(44)+char(32),char(44))
from
(select distinct COl1 from Tab) a
cross apply
(select Col2=(select COl2 from Tab where COl1=a.COl1 FOR XML AUTO, TYPE)
.query('<Tab>
{for $i in /Tab[position()<last()]/@COl2 return concat(string($i),",")}
{concat("",string(/Tab[last()]/@COl2))}
</Tab>')
)bSQL05用CTE:;with roy as(select Col1,Col2,row=row_number()over(partition by COl1 order by COl1) from Tab)
,Roy2 as
(select COl1,cast(COl2 as nvarchar(100))COl2,row from Roy where row=1
union all
select a.Col1,cast(b.COl2+','+a.COl2 as nvarchar(100)),a.row from Roy a join Roy2 b on a.COl1=b.COl1 and a.row=b.row+1)
select Col1,Col2 from Roy2 a where row=(select max(row) from roy where Col1=a.COl1) order by Col1 option (MAXRECURSION 0)
生成结果:
/*
Col1 COl2
----------- ------------
1 a,b,c
2 d,e
3 f(3 行受影响)
*/
(
fdate varchar(10),
fcus varchar(20),
qty decimal(18,4)
)
insert into tmp
select '2011-10-01','kh001',100
insert into tmp
select '2011-10-01','kh002',300
insert into tmp
select '2011-10-02','kh001',300
insert into tmp
select '2011-10-03','kh002',300
GO
--SQL2000用函数:go
if object_id('F_Str') is not null
drop function F_Str
go
create function F_Str(@fdate varchar(10))
returns nvarchar(100)
as
begin
declare @S nvarchar(100)
select @S=isnull(@S+';','')+fcus+':'+RTRIM(qty ) from tmp where fdate=@fdate
return @S
end
go
-- qty decimal(18,4)保留4位小數時SELECT fdate,SUM(qty) AS qty,
dbo.F_Str(fdate) AS fcus
FROM
tmp AS a
GROUP BY fdate/*
fdate qty fcus
2011-10-01 400.0000 kh001:100.0000;kh002:300.0000
2011-10-02 300.0000 kh001:300.0000
2011-10-03 300.0000 kh002:300.0000
*/