create table #(id int) insert into # select 1 insert into # select 2 insert into # select 3 insert into # select 4 insert into # select 5 insert into # select 6 go declare @sql varchar(2000) set @sql=''select @sql=@sql+','''+rtrim(id)+'''' from #set @sql='select '+stuff(@sql,1,1,'')exec(@sql) go /* ---- ---- ---- ---- ---- ---- 1 2 3 4 5 6 */drop table # go
select max(case col when 1 then col else null end), max(case col when 2 then col else null end) from table
例子.. a b 1 1 1 2 1 3 2 1 2 2 3 1 --如何转换成如下结果: a b 1 123 2 12 3 1 create table tb ( a int, b int ) insert into tb(a,b) values(1,1) insert into tb(a,b) values(1,2) insert into tb(a,b) values(1,3) insert into tb(a,b) values(2,1) insert into tb(a,b) values(2,2) insert into tb(a,b) values(3,1) gocreate function f_hb(@a int) returns varchar(8000) as begin declare @str varchar(8000) set @str = ' ' select @str = @str + '' + cast(b as varchar) from tb where a = @a set @str = right(@str , len(@str) - 1) return(@str) End go select distinct a ,dbo.f_hb(a) as b from tbdrop table tb--结果 a b ----------- ------ 1 123 2 12 3 1
to: libin_ftsafe(子陌红尘:当libin告别ftsafe) 如果表中的数据量大的话怎么办?
insert into # select 1
insert into # select 2
insert into # select 3
insert into # select 4
insert into # select 5
insert into # select 6
go
declare @sql varchar(2000)
set @sql=''select @sql=@sql+','''+rtrim(id)+'''' from #set @sql='select '+stuff(@sql,1,1,'')exec(@sql)
go
/*
---- ---- ---- ---- ---- ----
1 2 3 4 5 6
*/drop table #
go
a b
1 1
1 2
1 3
2 1
2 2
3 1
--如何转换成如下结果:
a b
1 123
2 12
3 1
create table tb
(
a int,
b int
)
insert into tb(a,b) values(1,1)
insert into tb(a,b) values(1,2)
insert into tb(a,b) values(1,3)
insert into tb(a,b) values(2,1)
insert into tb(a,b) values(2,2)
insert into tb(a,b) values(3,1)
gocreate function f_hb(@a int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ' '
select @str = @str + '' + cast(b as varchar) from tb where a = @a
set @str = right(@str , len(@str) - 1)
return(@str)
End
go
select distinct a ,dbo.f_hb(a) as b from tbdrop table tb--结果
a b
----------- ------
1 123
2 12
3 1
1 1 1 2 2 3
1 2 3 1 2 1 这样的结果.
insert T select 1
insert T select 2
insert T select 3
insert T select 4
insert T select 5declare @sql varchar(8000)
set @sql='select '
select @sql=@sql+quotename(id)+'='+rtrim(id)+',' from T
select @sql=left(@sql, len(@sql)-1)
exec(@sql)
--result
1 2 3 4 5
----------- ----------- ----------- ----------- -----------
1 2 3 4 5
我并不是要把它转换到表里, 而是直接显示的就可以了, 所以应该不受列数的限制.
如果这个sql语句超过8000个字符怎么办? 麻烦指点, 谢谢.
to: marco08(天道酬勤)
我并不是要把它转换到表里, 而是直接显示的就可以了, 所以应该不受列数的限制.
------------
我的沒有把結果转换到表里, 而是直接显示的
marco08(天道酬勤) 和 libin_ftsafe(子陌红尘:当libin告别ftsafe) 都是动态执行sql语句,
如果这个sql语句超过8000个字符怎么办? 麻烦指点, 谢谢.
-----------------
如果sql语句超过8000个字符,則用多個變量保存SQL語句
如exec(@sql1+@sql2+@sql3+...)