现有有表
5001 a
5001 b
5001 d
5002 e
5002 a
想得到以下结果
5001 a&b&d
5002 a&e
5001 a
5001 b
5001 d
5002 e
5002 a
想得到以下结果
5001 a&b&d
5002 a&e
解决方案 »
- 请问写出来sql报表能不能显示背景颜色
- access数据库 遇到空值的排序
- *******************请问关于MSDE和Sqlserver 2005 express的问题*****************
- 求SQL语句,有点难度(百分)
- 没有足够的系统内存来运行此查询。
- 触发器求救!
- 请问,我接触过一些Access,没接触过SQL Server,如果想通过使用SQL server,大约需要多久能基本上手?谢谢!
- 查询新用户注册后0-30或30-60天支出( 急!)
- SQL重复值,去除问题
- 一个有关文本相加的问题
- 可以自定义类型,造成的数据统计难题
- 在jet sql语言中如何实现reverse函数功能
DECLARE tb CURSOR LOCAL
FOR
SELECT col1,col2 FROM tb ORDER BY col1,col2
DECLARE @col1_old varchar(10),@col1 varchar(10),@col2 int,@s varchar(100)
OPEN tb
FETCH tb INTO @col1,@col2
SELECT @col1_old=@col1,@s=''
WHILE @@FETCH_STATUS=0
BEGIN
IF @col1=@col1_old
SELECT @s=@s+'&'+CAST(@col2 as varchar)
ELSE
BEGIN
INSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))
SELECT @s='&'+CAST(@col2 as varchar),@col1_old=@col1
END
FETCH tb INTO @col1,@col2
END
INSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))
CLOSE tb
DEALLOCATE tb
--显示结果并删除测试数据
SELECT * FROM @t
DROP TABLE tb
5001 a
5001 b
5001 d
5002 e
5002 a
create function f_str(@a int)
returns varchar(100)
as
begin
declare @s varchar(1000)
select @s = isnull(@s+'&','')+b from ta where a = @a
return @s
end
go
select a,dbo.f_str(a)
from ta
group by a
if object_id('test') is not null drop table test
create table tb
(col1 int,
col2 varchar(20))insert into tb
select 5001,'a' union
select 5001,'b' union
select 5001,'d' union
select 5002,'e' union
select 5002,'a'DECLARE @t TABLE(col1 varchar(10),col2 varchar(100))
DECLARE tb CURSOR LOCAL
FOR
SELECT col1,col2 FROM tb ORDER BY col1,col2
DECLARE @col1_old varchar(10),@col1 varchar(10),@col2 varchar(10),@s varchar(100)
OPEN tb
FETCH tb INTO @col1,@col2
SELECT @col1_old=@col1,@s=''
WHILE @@FETCH_STATUS=0
BEGIN
IF @col1=@col1_old
SELECT @s=@s+'&'+CAST(@col2 as varchar)
ELSE
BEGIN
INSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))
SELECT @s='&'+CAST(@col2 as varchar),@col1_old=@col1
END
FETCH tb INTO @col1,@col2
END
INSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))
CLOSE tb
DEALLOCATE tb
--显示结果并删除测试数据
SELECT * FROM @t
DROP TABLE tb--------------
5001 a&b&d
5002 a&e
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2010-01-21 15:15:46
-- 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]([id] int,[value] varchar(1))
insert [tb]
select 5001,'a' union all
select 5001,'b' union all
select 5001,'d' union all
select 5002,'e' union all
select 5002,'a'
--------------开始查询--------------------------
select
id, [values]=stuff((select ','+[value] from tb t where id=tb.id for xml path('')), 1, 1, '')
from
tb
group by
id
----------------结果----------------------------
/* id values
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5001 a,b,d
5002 e,a(2 行受影响)
*/
create table tablea
(
code varchar(5),
col varchar(2)
)
goinsert into tablea
select '5001', 'a' union all
select '5001', 'b' union all
select '5001', 'd' union all
select '5002', 'e' union all
select '5002', 'a'
go
;with t as
(select code,(select col+',' from tablea where code=a.code for xml path('')) as newcol from tablea a group by code)
select code,stuff(newcol,len(newcol),1,'')as col from t