一列转一行

/*
表tb
col 
11
aa
cc

想要的结果
s1,s2,s3 --显示字段名
11,aa,cc --数据结果
*/
/***************临时表解决方案****************************************************************************************/
-- 创建数据
if Exists(select * from sysobjects a where xtype = 'U' and name ='tb')
begin
drop table tb
end
go
create table tb
(
col nvarchar(100)
)
go
insert into tb
select '11'
union all select 'aa'
union all select '22'
union all select 'ccd'
go
-- 显示原始数据
select * from tb

-- 如果存在临时表，则先删除
if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#t'))
begin 
drop table #t
end

go

-- 创建临时表
select top 1 col s1 into #t from tb
go
-- 记录字段安徽
declare @count int
select @count= count(*) from tb

-- 创建所有表结构
declare @i int
set @i = 2
while (@i <= @count)
begin
	exec ('alter table #t add s'+ @i +' nvarchar(100) ;');
	set @i = @i + 1
end

-- 填入所有数据
set @i = 1
while (@i <= @count)
begin	
	declare @sql nvarchar(max)
	set @sql =	'update #t set s'+cast(@i as nvarchar(100))+' = (select col from (select *,row_number() over (order by getdate()) row from tb ) a where row = ' + cast( @i as nvarchar(100)) + ')';
	exec(@sql)
	set @i = @i + 1
end

-- 显示结果
select * from #t


/***************2005解决方案****************************************************************************************/
-- 创建数据
if Exists(select * from sysobjects a where xtype = 'U' and name ='tb')
begin
drop table tb
end
go
create table tb
(
col nvarchar(100)
)
go
insert into tb
select '11'
union all select 'aa'
union all select '22'
union all select '55'
union all select '23'
union all select 'ccd'
go
-- 显示原始数据
select * from tb

-- 如果存在临时表，则先删除
if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#t'))
begin 
drop table #t
end
-- 插入临时表
select col,'s'+ cast(row_number() over (order by getdate()) as nvarchar(10)) row  into #t from tb
-- 显示答案
declare @sql varchar(max)  
select @sql = isnull(@sql + ',' , '') + '['+row+']' from #t  
exec ('select * from #t pivot (max(col) for row in ('+@sql+')) b')


/***************游标解决方案****************************************************************************************/

create table melon(col varchar(20))
insert into melon values('11')
insert into melon values('aa')
insert into melon values('bb')
insert into melon values('66')
insert into melon values('dd')

declare @cc varchar(8000)
declare @cc1 varchar(20)
set @cc=''
set @cc1='s1'
declare @cur_col1 varchar(255) 

declare   cur_col1   cursor   for  
             select   col   from  melon 
open   cur_col1 
   fetch   next   from   cur_col1   into   @cur_col1 
   while   @@fetch_status   =   0   
   begin
      print @cc
      if @cc=''
      begin
        set @cc='select max(case when col='''+@cur_col1+''' then col end) as '+@cc1
     end
     else
        set @cc=@cc+',max(case when col='''+@cur_col1+''' then col end) as '+@cc1
        set @cc1=left(@cc1,1)+cast((cast(right(@cc1,len(@cc1)-1) as int)+1) as varchar(20))
        fetch   next   from   cur_col1   into   @cur_col1
   end 
   set @cc=@cc+' from melon'

close   cur_col1 
deallocate   cur_col1 
print @cc
 exec   (@cc)


/***************2000表变量拼接解决方案****************************************************************************************/
-- 创建数据
if Exists(select * from sysobjects a where xtype = 'U' and name ='tb')
begin
drop table tb
end
go
create table tb
(
col nvarchar(100)
)
go
insert into tb
select '11'
union all select 'aa'
union all select '22'
union all select '55'
union all select '23'
union all select 'ccd'
go
-- 显示原始数据
select * from tb

-- 记录字段安徽
declare @count int
select @count= count(*) from tb

-- 创建表变量并插入相应数据
declare @tab table
(
col nvarchar(10),
row int
)
insert into @tab select col, cast(row_number() over (order by getdate()) as nvarchar(10)) row   from tb;

-- 定义临时变量
declare @temp nvarchar(100)

-- 定义输入sql
declare @sql nvarchar(max)
set @sql = 'select '

-- 循环
declare @i int
set @i = 1
while (@i <= @count)
begin
	select top 1 @temp = col from @tab where row = @i
	set @sql  = @sql + '''' + @temp + ''' s'+cast(@i as nvarchar(100));
	if (@i != @count)
	begin
		set @sql = @sql + ',';
	end
	set @i = @i + 1
end
exec( @sql)


/***************2000精简方案****************************************************************************************/
if Exists(select * from sysobjects a where xtype = 'U' and name ='melon')
  drop table melon

create table melon(col varchar(20))
insert into melon values('11')
insert into melon values('aa')
insert into melon values('bb')
insert into melon values('cc')
insert into melon values('dd')

alter table melon add id int identity(1,1)

go
declare @s varchar(8000)
select @s=ISNULL(@s+',','') +''''+ col+''''+' as' +' s'+cast(id as varchar(20))   from melon  where id<>''
print @s
set @s='select ' +@s
exec(@s)

alter table melon drop column id

/***************2005无杂方案****************************************************************************************/

;WITH CTE (IDS) AS
(
SELECT ID A FROM Table WHERE KEY = 1 FOR XML PATH('')
)
SELECT ISNULL(REPLACE(REPLACE(IDS,'<A>',''),'</A>',' '),'') IDS FROM CTE