长沙 盘螺 HRB400 10mm
长沙 盘螺 HRB400 8mm
长沙 盘螺 HRB400 Ф10数据表中有以上3条记录,4个字段。
前3个字段的值都是一样的。我现在想将这三条记录合并成一条记录,如下:
长沙 盘螺 HRB400 10mm,8mm,Ф10请问用sql如何实现?谢谢!!
长沙 盘螺 HRB400 8mm
长沙 盘螺 HRB400 Ф10数据表中有以上3条记录,4个字段。
前3个字段的值都是一样的。我现在想将这三条记录合并成一条记录,如下:
长沙 盘螺 HRB400 10mm,8mm,Ф10请问用sql如何实现?谢谢!!
解决方案 »
- 随机字符
- 在存储过程中使用like时出现的问题
- 请教一下sqlserver存储过程递归有限制吗?
- EXECUTE master.dbo.xp_sqlmaint N'-PlanID 3E972558-622F-4013-B0B4-AEF1F55E347E -WriteHistory -RebldIdx 10 -RmUnusedSpace 50 10 '
- 求一select统计语句(续)
- 急求大虾:如何将SQLSERVER中的sql语句查询结果导出成txt文件???
- 帮帮我,怎样给表添上序号?
- OpenDataSource从CSV文件导入IP地址变成money,怎么回事?
- 连接数据库(大家帮帮忙,急等!)
- 如何在 SQL Server 里写 C++
- ASP运行SQL出现CPU100%,一直不下来,本机测试正常,远程运行很慢
- SQL 改变 字段类型 出错~ 求一SQL语句
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:[TB]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U'))
DROP TABLE [TB]
GO---->建表
create table [TB]([d] varchar(4),[s] varchar(4),[pp] varchar(6),[mm] varchar(4))
insert [TB]
select '长沙','盘螺','HRB400','10mm' union all
select '长沙','盘螺','HRB400','8mm' union all
select '长沙','盘螺','HRB400','Ф10'
GOSELECT a.[d],a.[s],a.[pp],
[mm] =stuff((
select ','+convert(varchar(20),[mm]) FROM [TB] WHERE [d] = a.[d] and [s] = a.[s] and [pp] = a.[pp] for xml path(''))
,1,1,'')
FROM [TB] a
group by a.[d],a.[s],a.[pp]
--> 查询结果
SELECT * FROM [TB]
--> 删除表格
--DROP TABLE [TB]
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:[TB]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U'))
DROP TABLE [TB]
GO---->建表
create table [TB]([d] varchar(4),[s] varchar(4),[pp] varchar(6),[mm] varchar(4))
insert [TB]
select '长沙','盘螺','HRB400','10mm' union all
select '长沙','盘螺','HRB400','8mm' union all
select '长沙','盘螺','HRB400','Ф10'
GO
SELECT a.[d],a.[s],a.[pp],
status =STUFF(REPLACE(REPLACE(
(select [mm]
FROM [TB]
where [d] = a.[d] and [s] = a.[s] and [pp] = a.[pp]
FOR XML AUTO
), '<TB mm="', ','), '"/>', ''), 1, 1, '')
FROM [TB] a
group by a.[d],a.[s],a.[pp]--> 查询结果
SELECT * FROM [TB]
--> 删除表格
--DROP TABLE [TB]
insert into tb select '长沙','盘螺','HRB400','10mm'
insert into tb select '长沙','盘螺','HRB400','10mm'
insert into tb select '长沙','盘螺','HRB400','10mm'
go
select ad,proj,xh,stuff((select ','+gg from tb where xh=a.xh for xml path('')),1,1,'') from tb a group by ad,proj,xh
go
drop table tb
/*
ad proj xh
----- ---------- ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
长沙 盘螺 HRB400 10mm,10mm,10mm*/
create table tb(ad nvarchar(5),proj nvarchar(10), xh nvarchar(10),gg nvarchar(10))
insert into tb select '长沙','盘螺','HRB400','10mm'
insert into tb select '长沙','盘螺','HRB400','10mm'
insert into tb select '长沙','盘螺','HRB400','10mm'
GO
--新建一个函数
create function dbo.f_str(@ad nvarchar(5),@proj nvarchar(10), @xh nvarchar(10)) returns varchar(1000)
as
begin
declare @str varchar(1000)
select @str = isnull(@str + ',' , '') + cast(gg as varchar) from tb where ad = @id and proj=@proj and xh=@xh
--print @str
return @str
end
select ad,proj,xh , value = dbo.f_str(ad,proj,xh) from tb group by ad,proj,xh
你只有使用
sql 2000/2005通用函数
sql 2005 for XML path