如何将某个字段的记录和成字符串输出

[大脚板]

或者能将行列倒置

最好不要用循环

[pengdali]

declare @a varhcar(8000)
set @a=''
select @a=列+','+@a from 表

select left(@a,len(@a)-1) 结果

[pengdali]

declare @a varchar(8000)
set @a=''
select @a=列+','+@a from 表

select left(@a,len(@a)-1) 结果


举例：

declare @a varchar(8000)
set @a=''
select @a=name+','+@a from sysobjects

select left(@a,len(@a)-1) 结果

[zjcxc]

--记录合成字符串

--测试表
declare @tb table(name varchar(10))
insert into @tb
select 'a'
union all select 'b'
union all select 'c'
union all select 'd'
union all select 'e'

--合并成字符串处理
declare @s varchar(8000)
set @s=''
select @s=@s+','+name from @tb
set @s=substring(@s,2,8000)
select '合并结果'+@s

[zjcxc]

--行列转换

--测试表
declare @tb table(name varchar(10),值 int)
insert into @tb
select 'a',1
union all select 'b',2
union all select 'c',3
union all select 'd',4
union all select 'e',5

--转换处理
declare @s varchar(8000)
set @s=''
select @s=@s+','+name+'='+cast(值 as varchar) from @tb
set @s=substring(@s,2,8000)
exec('select '+@s)

[大脚板]

我对sql比较弱

zjcxc(邹建)：3列的转化函数应该怎么写啊

[大脚板]

而且字符数可能会大于8000，应该怎么处理比较好

[pengdali]

zt:


if exists (select * from sysobjects where id = object_id('proc_sky_blue') and xtype ='P')
drop proc proc_sky_blue
go
create proc proc_sky_blue (@tablename varchar(200))
as
begin
set nocount on
declare @col nvarchar(256)
declare @makesql nvarchar(4000)
declare @insertsql nvarchar(4000)
declare @caculatesql nvarchar(400)
declare @count int
declare @i int
create table #tmp (colname nvarchar(20))
select @caculatesql = 'select @count=count(1) from ' + @tablename
exec sp_executesql @caculatesql, N'@count int output',@count output
if @count >=1024
begin
raiserror('表的行数太多了，我转不了',16,1)
end
else
begin
select @i=0
while @count >0
begin
select @i=@i+1
select @makesql = 'alter table #tmp add col'+convert(varchar(20),@i)+' int'
exec(@makesql)
select @count=@count-1
end
declare my_cursor cursor for
select name from syscolumns where id=object_id(@tablename) order by colid
open my_cursor
fetch next from my_cursor into @col
while @@fetch_status = 0
begin
select @makesql ='select @insertsql= @insertsql + convert(varchar(4),'+@col+') +'','' from ' +@tablename
select @insertsql =N'insert #tmp values ('''+@col+ ''','
execute sp_executesql @makesql,N'@insertsql nvarchar(4000) output' ,@insertsql output
select @insertsql = left(@insertsql,len(@insertsql)-1) +')'
exec(@insertsql)
fetch next from my_cursor into @col
end
close my_cursor
deallocate my_cursor
select * from #tmp
set nocount off
end
end

go
----------------分析
declare @tablename varchar(200)
set @tablename='table1'
begin
set nocount on
declare @col nvarchar(256)
declare @makesql nvarchar(4000)
declare @insertsql nvarchar(4000)
declare @caculatesql nvarchar(400)
declare @count int
declare @i int
create table #tmp (colname nvarchar(20))
select @caculatesql = 'select @count=count(1) from ' + @tablename
exec sp_executesql @caculatesql, N'@count int output',@count output
if @count >=1024
begin
raiserror('表的行数太多了，我转不了',16,1)
end
else
begin
select @i=0
while @count >0
begin
select @i=@i+1
select @makesql = 'alter table #tmp add col'+convert(varchar(20),@i)+' int'
exec(@makesql)
select @count=@count-1
end
declare my_cursor cursor for
select name from syscolumns where id=object_id(@tablename) order by colid
open my_cursor
fetch next from my_cursor into @col
while @@fetch_status = 0
begin
select @makesql ='select @insertsql= @insertsql + convert(varchar(4),'+@col+') +'','' from ' +@tablename
select @insertsql =N'insert #tmp values ('''+@col+ ''','
execute sp_executesql @makesql,N'@insertsql nvarchar(4000) output' ,@insertsql output
select @insertsql = left(@insertsql,len(@insertsql)-1) +')'
select @insertsql
--exec(@insertsql)
fetch next from my_cursor into @col
end
close my_cursor
deallocate my_cursor
select * from #tmp
set nocount off
drop table #tmp
end
end

[zjcxc]

--行列转换,3列的

--测试表
declare @tb table(name varchar(10),值 int,值1 int)
insert into @tb
select 'a',1,11
union all select 'b',2,22
union all select 'c',3,33
union all select 'd',4,44
union all select 'e',5,55

--转换处理
declare @s varchar(8000),@s1 varchar(8000)
select @s='',@s1=''
select @s=@s+','+name+'='+cast(值 as varchar)
,@s1=@s1+','+cast(值1 as varchar)
from @tb
select @s=substring(@s,2,8000)
,@s1=substring(@s1,2,8000)
exec('select '+@s+' union all select '+@s1)

[zjcxc]

结果:

（所影响的行数为 5 行）

a b c d e
----------- ----------- ----------- ----------- -----------
1 2 3 4 5
11 22 33 44 55

[大脚板]

多谢两位前辈指点