34,594
社区成员
发帖
与我相关
我的任务
分享
--> 生成测试数据表: [t_stockin]
IF OBJECT_ID('[t_stockin]') IS NOT NULL
DROP TABLE [t_stockin]
GO
CREATE TABLE [t_stockin] ([si_cwd] [nvarchar](20),[si_ino] [int],[si_cciwd] [nvarchar](20))
INSERT INTO [t_stockin]
SELECT '0709-0000159','1','0709-0000128' UNION ALL
SELECT '0709-0000159','2','0709-0000128'
--> 生成测试数据表: [t_commin]
IF OBJECT_ID('[t_commin]') IS NOT NULL
DROP TABLE [t_commin]
GO
CREATE TABLE [t_commin] ([ci_cwd] [nvarchar](20))
INSERT INTO [t_commin]
SELECT '0709-0000128'
--2005的直接查询:
SELECT *,
cwd_str=STUFF((SELECT ','+LTRIM(si_cwd) FROM [t_stockin] WHERE [si_cciwd]=t.[ci_cwd] FOR XML PATH('')),1,1,''),
into_str=STUFF((SELECT ','+LTRIM(si_ino) FROM [t_stockin] WHERE [si_cciwd]=t.[ci_cwd] FOR XML PATH('')),1,1,'')
FROM [t_commin] t
/*
ci_cwd cwd_str into_str
-------------------- ------------------------------ ------------------------------
0709-0000128 0709-0000159,0709-0000159 1,2
(1 行受影响)
*/
----------------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-06-07 09:00:24
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
----------------------------------------------------------------------------------
--> 生成测试数据表: [t_stockin]
IF OBJECT_ID('[t_stockin]') IS NOT NULL
DROP TABLE [t_stockin]
GO
CREATE TABLE [t_stockin] ([si_cwd] [nvarchar](20),[si_ino] [int],[si_cciwd] [nvarchar](20))
INSERT INTO [t_stockin]
SELECT '0709-0000159','1','0709-0000128' UNION ALL
SELECT '0709-0000159','2','0709-0000128'
--> 生成测试数据表: [t_commin]
IF OBJECT_ID('[t_commin]') IS NOT NULL
DROP TABLE [t_commin]
GO
CREATE TABLE [t_commin] ([ci_cwd] [nvarchar](20))
INSERT INTO [t_commin]
SELECT '0709-0000128'
--SELECT * FROM [t_stockin]
--SELECT * FROM [t_commin]
-->SQL查询如下:
IF OBJECT_ID('dbo.f_str') IS NOT NULL
DROP FUNCTION dbo.f_str
GO
CREATE FUNCTION dbo.f_str
(
@si_cciwd VARCHAR(200),
@type int
)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @str VARCHAR(1000)
SELECT @str = ISNULL(@str+',', '')
+ CASE @type
WHEN 1 THEN LTRIM(si_ino)
WHEN 2 THEN LTRIM([si_cwd])
END
FROM [t_stockin]
WHERE [si_cciwd] = @si_cciwd
RETURN @str
END
GO
SELECT *,dbo.f_str(ci_cwd,2) cwd_str,dbo.f_str(ci_cwd,1) into_str FROM [t_commin]
/*
ci_cwd cwd_str into_str
-------------------- ------------------------------ ------------------------------
0709-0000128 0709-0000159,0709-0000159 1,2
(1 行受影响)
*/
MODIFY----------------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-06-07 09:00:24
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
----------------------------------------------------------------------------------
--> 生成测试数据表: [t_stockin]
IF OBJECT_ID('[t_stockin]') IS NOT NULL
DROP TABLE [t_stockin]
GO
CREATE TABLE [t_stockin] ([si_cwd] [nvarchar](20),[si_ino] [int],[si_cciwd] [nvarchar](20))
INSERT INTO [t_stockin]
SELECT '0709-0000159','1','0709-0000128' UNION ALL
SELECT '0709-0000159','2','0709-0000128'
--> 生成测试数据表: [t_commin]
IF OBJECT_ID('[t_commin]') IS NOT NULL
DROP TABLE [t_commin]
GO
CREATE TABLE [t_commin] ([ci_cwd] [nvarchar](20))
INSERT INTO [t_commin]
SELECT '0709-0000128'
--SELECT * FROM [t_stockin]
--SELECT * FROM [t_commin]
-->SQL查询如下:
IF OBJECT_ID('dbo.f_str') IS NOT NULL
DROP FUNCTION dbo.f_str
GO
CREATE FUNCTION dbo.f_str
(
@si_cciwd VARCHAR(20),
@type int
)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @str VARCHAR(1000)
SELECT @str = ISNULL(@str+',', '')
+ CASE @type
WHEN 1 THEN LTRIM(si_ino)
WHEN 2 THEN LTRIM([si_cciwd])
END
FROM [t_stockin]
WHERE [si_cciwd] = @si_cciwd
RETURN @str
END
GO
SELECT *,dbo.f_str(ci_cwd,1) [si_ino],dbo.f_str(ci_cwd,2) [si_cciwd] FROM [t_commin]
/*
ci_cwd si_ino si_cciwd
-------------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
0709-0000128 1,2 0709-0000128,0709-0000128
(1 行受影响)
*/
--1)存储过程
DROP PROC [dbo].[sp_merge_str]
Go
CREATE PROC [dbo].[sp_merge_str]
@cwd VARCHAR(30),
@field VARCHAR(30)
AS
BEGIN
DECLARE @strsql NVARCHAR(4000)
DECLARE @str VARCHAR(8000)
SET @str = ''
SET @strsql = ' SELECT @str = ISNULL(@str + '','','''') + cast( ' + @field + ' as varchar) FROM dbo.a_b WHERE aid = ''' + @cwd + ''''
EXECUTE sp_executesql @strsql, N'@str varchar(8000) output', @str output
SELECT merge_str = SUBSTRING(@str,2,LEN(@str)-1),aid = @cwd
END
--2)a_b相关数据
SELECT * FROM a_b
aID bID
---------- -----------
01 10
02 20
02 30
01 20
01 30
(5 row(s) affected)
--3)执行
EXEC sp_merge_str
@cwd = '01',
@field ='bid'
--4)结果
merge_str aid
10,20,30 01
(1 row(s) affected)
create function sp_merge_str(@cwd VARCHAR(30),@field VARCHAR(30))
return VARCHAR(8000)
AS
BEGIN
DECLARE @strsql NVARCHAR(4000)
SET @str = ''
SELECT @str = @str + ',' + cast( @field as varchar) FROM dbo.t_stockin WHERE si_cciwd = @cwd
set @str=right(@str,len(@str)-1)
return(@str)
END
SELECT ci_cwd, ci_iNo, = dbo.sp_merge_str(ci_cwd,'si_cwd') as si_cwd_Str
,dbo.sp_merge_str (ci_iNo,'si_ino' ) as si_iNO_Str
FROM t_commin
create function [dbo].[sp_merge_str]
@cwd VARCHAR(30),
@field VARCHAR(30)
@str VARCHAR(8000) output
AS
BEGIN
DECLARE @strsql NVARCHAR(4000)
SET @str = ''
SET @strsql = ' SELECT @str = @str + '','' + cast( ' + @field + ' as varchar) FROM dbo.t_stockin WHERE si_cciwd = ''' + @cwd + ''''
exec(@strsql)
END
SELECT ci_cwd, ci_iNo, = dbo.sp_merge_str(ci_cwd,'si_cwd') as si_cwd_Str
,dbo.sp_merge_str (ci_iNo,'si_ino' ) as si_iNO_Str
FROM t_commin
create function [dbo].[sp_merge_str]
@cwd VARCHAR(30),
@field VARCHAR(30)
AS
BEGIN
DECLARE @strsql NVARCHAR(4000)
SET @str = ''
SET @strsql = ' SELECT @str = @str + '','' + cast( ' + @field + ' as varchar) FROM dbo.t_stockin WHERE si_cciwd = ''' + @cwd + ''''
exec(@strsql)
END
SELECT ci_cwd, ci_iNo, = dbo.sp_merge_str(ci_cwd,'si_cwd') as si_cwd_Str
,dbo.sp_merge_str (ci_iNo,'si_ino' ) as si_iNO_Str
FROM t_commin