insert into test_g select 'a','b','1' union all select 'a','c','2' union all select 'd','e','3' union all select 'd','f','4' union all select 'd','g','5'select ca, 合并列 = (select cb+'',cc + '' from test_g where ca = a.ca for xml path('') ) from test_g a group by caca 合并列 ---- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- a b1c2 d e3f4g5(2 row(s) affected)
create table CCC ( ca varchar(20), cb varchar(20), cc int ) insert into ccc select 'a','b',1 union all select 'a','c',2 union all select 'd','e',3 union all select 'd','f',4 union all select 'd','g',5 drop function dbo.f_str CREATE FUNCTION dbo.f_str(@id varchar(20)) RETURNS varchar(8000) AS BEGIN DECLARE @r varchar(8000) SET @r = '' SELECT @r = @r +''+ cb+cast(cc as varchar) FROM CCC WHERE ca=@id RETURN STUFF(@r, 1, 1, '') END GO select distinct(ca),dbo.f_str(ca) 'Names ' from ccc
修改下 create table CCC ( ca varchar(20), cb varchar(20), cc int ) insert into ccc select 'a','b',1 union all select 'a','c',2 union all select 'd','e',3 union all select 'd','f',4 union all select 'd','g',5 drop function dbo.f_str CREATE FUNCTION dbo.f_str(@id varchar(20)) RETURNS varchar(8000) AS BEGIN DECLARE @r varchar(8000) SET @r = '' SELECT @r = ca+@r +''+ cb+cast(cc as varchar) FROM CCC WHERE ca=@id RETURN STUFF(@r, 1, 1, '') END GO select distinct(dbo.f_str(ca)) 'Names ' from ccc Names ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ab1c2 dde3f4g5(2 行受影响)
create table test_g (ca nvarchar(2),cb nvarchar(2),cc nvarchar(2))insert into test_g select 'a','b','1' union all select 'a','c','2' union all select 'd','e','3' union all select 'd','f','4' union all select 'd','g','5'select 合并列 = (select ca+' ', cb+' ',cc + ' ' from test_g where ca = a.ca for xml path('') ) from test_g a group by ca合并列 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- a b 1 a c 2 d e 3 d f 4 d g 5 (2 row(s) affected)
create table table1(CA varchar(4),CB varchar(4),CC varchar(4)) INSERT INTO table1 values('a','b','1') INSERT INTO table1 values('a','c','2') INSERT INTO table1 values('d','e','3') INSERT INTO table1 values('d','f','4') INSERT INTO table1 values('d','g','5') select * from table1select CA, 合并列 = (select CB+'',CC + '' from table1 where CA = a.CA for xml path('') ) from table1 a group by CA/* CA 合并列 ---- ----- a b1c2 d e3f4g5(2 行受影响) */
ca 合并列 ---- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- a b1c2 d e3f4g5(2 row(s) affected)合并列可不可以有放在不同的列里,如: CA CB1,CC1,CB2,CC2...... a b 1 c 2 d e 3 f 4 g 5
---测试数据--- if object_id('[TA]') is not null drop table [TA] go create table [TA]([CA] varchar(1),[CB] varchar(1),[CC] int) insert [TA] select 'a','b',1 union all select 'a','c',2 union all select 'd','e',3 union all select 'd','f',4 union all select 'd','g',5---创建字符连接函数--- create function F_Str(@CA VARCHAR(10)) returns nvarchar(100) as begin declare @S nvarchar(100) select @S=isnull(@S+' ','')+CB+' '+ LTRIM(CC) from TA where CA=@CA return @S end---查询--- select DISTINCT CA+' '+dbo.F_Str(CA) AS COL from [TA]---结果--- COL ------------------------------------------------------------------------------------------------------ a b 1 c 2 d e 3 f 4 g 5(所影响的行数为 2 行)
if object_id('tb') is not null drop table tb go create table tb(CA varchar(10),CB varchar(10),CC int) insert tb select 'a','b',1 insert tb select 'a','c',2 insert tb select 'd','e',3 insert tb select 'd','f',4 insert tb select 'd','g',5 go if object_id('get_str') is not null drop function get_str go create function get_str(@ca varchar(10)) returns varchar(30) as begin declare @s varchar(30) select @s=isnull(@s,'')+cb+' '+ltrim(cc)+' ' from tb where ca=@ca return @s end goselect distinct ca,dbo.get_str(ca) col from tb /* ca col ---------- ------------------------------ a b 1 c 2 d e 3 f 4 g 5 (2 行受影响) */
CREATE TABLE test112(a VARCHAR(10),b VARCHAR(10),c INT) INSERT test112 SELECT 'a','b','1' union all select 'a','c','2' union all select 'd','e','3' union all select 'd','f','4' union all select 'd','g','5' go CREATE FUNCTION GColString(@a VARCHAR(10)) RETURNS VARCHAR(1000) AS BEGIN DECLARE @s VARCHAR(1000) SELECT @s=ISNULL(@s+'|','')+ltrim(b)+'|'+LTRIM(c) FROM test112 t WHERE a=@a RETURN @s+'|' END goSELECT a,dbo.GColString(a) MESSAGE INTO tk FROM test112 GROUP BY adeclare @max int,@i int declare @sql varchar(8000)set @i=1 select @max=max(len(message)-len(replace(message,'|',''))) from tkselect a,message+replicate('|',@max-len(message)+len(replace(message,'|',''))) message into tkx from tkwhile @i<=@max begin select @sql=isnull(@sql+',','')+'col'+ltrim(@i)+' varchar(50)',@i=@i+1 end set @sql='create table tkxx(a varchar(10),'+@sql+')' exec(@sql)set @i=1 insert tkxx(a) select a from tkxwhile @i<=@max begin set @sql=' update a set col'+ltrim(@i)+'=left(message,charindex(''|'',message)-1) from tkxx a inner join tkx b on a.a=b.a ; update tkx set message=stuff(message,1,charindex(''|'',message),'''');' exec(@sql) set @i=@i+1 endselect * from tkxxdrop table tk,tkx,tkxx --result /*a col1 col2 col3 col4 col5 col6 ---------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ a b 1 c 2 d e 3 f 4 g 5(所影响的行数为 2 行) */
服务端sqlserver 2000 客户端sqlserver 2005
这样查询还得用SQL2000的,只能用函数了.
=================================================================== 能解释一下 for xml path('')的作用吗? 我用的是SQL 2000怎么 说xml 附近有语法错误?
insert into test_g
select 'a','b','1'
union all
select 'a','c','2'
union all
select 'd','e','3'
union all
select 'd','f','4'
union all
select 'd','g','5'select ca,
合并列 = (select cb+'',cc + '' from test_g where ca = a.ca for xml path('') )
from test_g a group by caca 合并列
---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
a b1c2
d e3f4g5(2 row(s) affected)
(
ca varchar(20),
cb varchar(20),
cc int
)
insert into ccc select 'a','b',1
union all select 'a','c',2
union all select 'd','e',3
union all select 'd','f',4
union all select 'd','g',5
drop function dbo.f_str
CREATE FUNCTION dbo.f_str(@id varchar(20))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r = ''
SELECT @r = @r +''+ cb+cast(cc as varchar) FROM CCC
WHERE ca=@id
RETURN STUFF(@r, 1, 1, '')
END
GO
select distinct(ca),dbo.f_str(ca) 'Names ' from ccc
create table CCC
(
ca varchar(20),
cb varchar(20),
cc int
)
insert into ccc select 'a','b',1
union all select 'a','c',2
union all select 'd','e',3
union all select 'd','f',4
union all select 'd','g',5
drop function dbo.f_str
CREATE FUNCTION dbo.f_str(@id varchar(20))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r = ''
SELECT @r = ca+@r +''+ cb+cast(cc as varchar) FROM CCC
WHERE ca=@id
RETURN STUFF(@r, 1, 1, '')
END
GO
select distinct(dbo.f_str(ca)) 'Names ' from ccc
Names
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ab1c2
dde3f4g5(2 行受影响)
create table test_g
(ca nvarchar(2),cb nvarchar(2),cc nvarchar(2))insert into test_g
select 'a','b','1'
union all
select 'a','c','2'
union all
select 'd','e','3'
union all
select 'd','f','4'
union all
select 'd','g','5'select
合并列 = (select ca+' ', cb+' ',cc + ' ' from test_g where ca = a.ca for xml path('') )
from test_g a group by ca合并列
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
a b 1 a c 2
d e 3 d f 4 d g 5 (2 row(s) affected)
create table table1(CA varchar(4),CB varchar(4),CC varchar(4))
INSERT INTO table1 values('a','b','1')
INSERT INTO table1 values('a','c','2')
INSERT INTO table1 values('d','e','3')
INSERT INTO table1 values('d','f','4')
INSERT INTO table1 values('d','g','5')
select * from table1select CA,
合并列 = (select CB+'',CC + '' from table1 where CA = a.CA for xml path('') )
from table1 a group by CA/*
CA 合并列
---- -----
a b1c2
d e3f4g5(2 行受影响)
*/
---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
a b1c2
d e3f4g5(2 row(s) affected)合并列可不可以有放在不同的列里,如:
CA CB1,CC1,CB2,CC2......
a b 1 c 2
d e 3 f 4 g 5
if object_id('[TA]') is not null drop table [TA]
go
create table [TA]([CA] varchar(1),[CB] varchar(1),[CC] int)
insert [TA]
select 'a','b',1 union all
select 'a','c',2 union all
select 'd','e',3 union all
select 'd','f',4 union all
select 'd','g',5---创建字符连接函数---
create function F_Str(@CA VARCHAR(10))
returns nvarchar(100)
as
begin
declare @S nvarchar(100)
select @S=isnull(@S+' ','')+CB+' '+ LTRIM(CC) from TA where CA=@CA
return @S
end---查询---
select
DISTINCT
CA+' '+dbo.F_Str(CA) AS COL
from
[TA]---结果---
COL
------------------------------------------------------------------------------------------------------
a b 1 c 2
d e 3 f 4 g 5(所影响的行数为 2 行)
drop table tb
go
create table tb(CA varchar(10),CB varchar(10),CC int)
insert tb select 'a','b',1
insert tb select 'a','c',2
insert tb select 'd','e',3
insert tb select 'd','f',4
insert tb select 'd','g',5
go
if object_id('get_str') is not null
drop function get_str
go
create function get_str(@ca varchar(10))
returns varchar(30)
as
begin
declare @s varchar(30)
select @s=isnull(@s,'')+cb+' '+ltrim(cc)+' ' from tb where ca=@ca
return @s
end
goselect distinct ca,dbo.get_str(ca) col from tb
/*
ca col
---------- ------------------------------
a b 1 c 2
d e 3 f 4 g 5 (2 行受影响)
*/
INSERT test112 SELECT 'a','b','1'
union all select 'a','c','2'
union all select 'd','e','3'
union all select 'd','f','4'
union all select 'd','g','5' go
CREATE FUNCTION GColString(@a VARCHAR(10))
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @s VARCHAR(1000)
SELECT @s=ISNULL(@s+'|','')+ltrim(b)+'|'+LTRIM(c) FROM test112 t WHERE a=@a
RETURN @s+'|'
END
goSELECT a,dbo.GColString(a) MESSAGE INTO tk FROM test112 GROUP BY adeclare @max int,@i int
declare @sql varchar(8000)set @i=1
select @max=max(len(message)-len(replace(message,'|',''))) from tkselect a,message+replicate('|',@max-len(message)+len(replace(message,'|',''))) message into tkx from tkwhile @i<=@max
begin
select @sql=isnull(@sql+',','')+'col'+ltrim(@i)+' varchar(50)',@i=@i+1
end
set @sql='create table tkxx(a varchar(10),'+@sql+')'
exec(@sql)set @i=1
insert tkxx(a) select a from tkxwhile @i<=@max
begin
set @sql='
update a set col'+ltrim(@i)+'=left(message,charindex(''|'',message)-1) from tkxx a inner join tkx b on a.a=b.a ;
update tkx set message=stuff(message,1,charindex(''|'',message),'''');'
exec(@sql)
set @i=@i+1
endselect * from tkxxdrop table tk,tkx,tkxx
--result
/*a col1 col2 col3 col4 col5 col6
---------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
a b 1 c 2
d e 3 f 4 g 5(所影响的行数为 2 行)
*/
客户端sqlserver 2005
能解释一下 for xml path('')的作用吗? 我用的是SQL 2000怎么 说xml 附近有语法错误?