解决方案 »
- sql sever 怎么取离当前时间最近的数据
- 我的数据库文件超过3G,但是我用“exec sp_spaceused ”显示有 2674056 KB 的 unused ,怎么去掉?
- sql server200 sql查询分析器用不了
- 求一Sql语句,有力出力啊,解决立马结分。
- 俺写的 SQLServer中取出表A中第31到第40记录
- [分享]怎么捕获和记录SQL Server中发生的死锁?
- 一个匹配的问题?(解决马上给分!)
- 动态取消 IDENTITY
- 这个存储过程错在哪里?
- 在VB里如何通过程序将sql server某张表的默认值等属性读出来。
- sql 2012 中ssis包问题???????求解????
- SQL内连接后有空值不显示结果
from #t1 as a
/*
orderno idno
-------------------- -------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
N001 A001 N002/N004/N005/
N001 A002 N003/N004/N006/
N001 A003
N002 A001 N001/N004/N005/
N003 A002 N001/N004/N006/
N004 A001 N001/N002/N005/
N004 A002 N001/N003/N006/
N005 A001 N001/N002/N004/
N006 A002 N001/N003/N004/
N007 A011 (10 行受影响)*/
你确定?
合并分拆表数据 整理人:中国风(Roy) 日期:2008.06.06
******************************************************************************************************************************************************/ --> --> (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 Tab go 参考下吧 没环境没办法测试。
CREATE FUNCTION dbo.f_strUnite(@id varchar(20))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @str varchar(8000)
SET @str = ''
SELECT @str = @str + '/' + orderno FROM t1 WHERE idno=@id AND ORDERNO<>@id
--SET @STR=lTRIM(STUFF(@str, 1, 1, ''))
RETURN @STR
END
GO
select orderno,idno,STUFF(REPLACE(dbo.f_strUnite(idno),'/'+ORDERNO,''),1,1,'')AS U from t1--orderno idno U
--N001 A001 N002/N004/N005
--N001 A002 N003/N004/N006
--N001 A003 NULL
--N002 A001 N001/N004/N005
--N003 A002 N001/N004/N006
--N004 A001 N001/N002/N005
--N004 A002 N001/N003/N006
--N005 A001 N001/N002/N004
--N006 A002 N001/N003/N004
--N007 A011 NULL