表一:
gldw
--------------------------------------------------
Qnbgldw0001,Qnbgldw0002,Qnbgldw0003
Qnbgldw0001,Qnbgldw0002
Qnbgldw0001,Qnbgldw0002
Qnbgldw0001,Qnbgldw0002
Qnbgldw0001,Qnbgldw0002表二:
gldwid gldwmc
-------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Qnbgldw0001 市直管
Qnbgldw0002 姚江
Qnbgldw0003 原水集团
我有这样两张表,我想把表一的gldw里的字段换成表二中的gldwmc,
也是用“,”隔开(通过一自定义函数)
请高手指教,先谢谢了
gldw
--------------------------------------------------
Qnbgldw0001,Qnbgldw0002,Qnbgldw0003
Qnbgldw0001,Qnbgldw0002
Qnbgldw0001,Qnbgldw0002
Qnbgldw0001,Qnbgldw0002
Qnbgldw0001,Qnbgldw0002表二:
gldwid gldwmc
-------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Qnbgldw0001 市直管
Qnbgldw0002 姚江
Qnbgldw0003 原水集团
我有这样两张表,我想把表一的gldw里的字段换成表二中的gldwmc,
也是用“,”隔开(通过一自定义函数)
请高手指教,先谢谢了
returns nvarchar(4000)
as
begin
declare @t table(col varchar(20))
while(charindex(@split,@c)<>0)
begin
insert @t(col) values (substring(@c,1,charindex(@split,@c)-1))
set @c = stuff(@c,1,charindex(@split,@c),'')
end
insert @t(col) values (@c)
declare @S nvarchar(4000)
select @S=isnull(@S+',','')+rtrim(gldwmc) from @t inner join gldw on gldwid=col
return @S
end
代码如下
功能实现了,不知道效率怎么样
declare @表 table (gldw nvarchar(100))
insert into @表 select 'qnbgldw0001,qnbgldw0002,qnbgldw0003'
union all select 'qnbgldw0001,qnbgldw0002'
union all select 'qnbgldw0001,qnbgldw0002'
union all select 'qnbgldw0001,qnbgldw0002'
union all select 'qnbgldw0001,qnbgldw0002'
update @表 set gldw=REPLACE(gldw,'qnbgldw0001','市直管')
update @表 set gldw=REPLACE(gldw,'qnbgldw0002','姚江')
update @表 set gldw=REPLACE(gldw,'qnbgldw0003','原水集团')
select * from @表
这样写sql语句不是很长
---------------------------------
-- Author: liangCK 小梁
-- Date : 2008-11-14 19:03:48
---------------------------------
--> 生成测试数据: #tb
CREATE TABLE #tb(col VARCHAR(50))
INSERT INTO #tb
SELECT 'aa,bb,cc' UNION ALL
SELECT 'AAA,BB' UNION ALL
SELECT 'AAA'--SQL查询如下:DECLARE @SQL VARCHAR(8000)
DECLARE @i INT,@m INT
SET @SQL=''
SET @m=(SELECT MAX([length])+1
FROM(
SELECT (LEN(col)-LEN(REPLACE(col,',','')))/LEN(',') AS [length]
FROM #tb
) AS A)
SET @i=1WHILE @i<=@m
BEGIN
SET @SQL=@SQL+',MAX(CASE WHEN cid='+CAST(@i AS VARCHAR)+' THEN data ELSE '''' END) AS col'+CAST(@i AS VARCHAR)
SET @i=@i+1
ENDEXEC('
WITH Liang AS
(
SELECT
col,
rid=ROW_NUMBER() OVER(ORDER BY GETDATE())
FROM #tb
),
Liang2 AS
(
SELECT
A.rid,
A.col,
B.data,
cid=ROW_NUMBER() OVER(PARTITION BY A.rid ORDER BY B.data)
FROM(
SELECT
col,
rid,
v=CONVERT(XML,''<v>''+REPLACE(col,'','',''</v><v>'')+''</v>'')
FROM Liang
) AS A
CROSS APPLY(
SELECT
x.value(''.'',''varchar(50)'') AS data
FROM A.v.nodes(''//v'') AS T(x)
) AS B
)
SELECT col'+@SQL+'
FROM Liang2
GROUP BY col'
)DROP TABLE #tb