--建立测试表 create table test ( name varchar(255) primary key, counter int not null ) go--插入测试数据 insert into test values('a',1) insert into test values('b',2) insert into test values('c',3)--查看测试数据 select * from test --生成如何把两列的数据检索成一行算法 --输出参数@show declare @name varchar(255),@counter int declare @show varchar(8000) declare @ int set @ = 0 declare test_cursor CURSOR FOR select name,counter from test open test_cursor fetch next from test_cursor into @name,@counter while @@fetch_status = 0 begin if(@ = 0) begin set @show = @name + ' '+convert(varchar(255),@counter) set @ = 1 end else begin set @show = @show + ' '+@name + ' '+convert(varchar(255),@counter) end fetch next from test_cursor into @name,@counter end close test_cursor deallocate test_cursor select @show as showcolumn GO--注:上述代码我只用了一个varchar显示数据,varchar最大长度只能是8000个字符,如果您的记录数>8000,您必须要用多个varchar做拼借进行一行的显示
select * into #tt from (select '1001' as id ,'项目1' as name union all select '1002' ,'项目2' union all select '1003' ,'项目3' union all select '1004' ,'项目4' union all select '1005' ,'项目5' union all select '1006' ,'项目6')a declare @a varchar(8000) set @a = '' select @a=@a +''''+id +''','''+ name +''',' from #tt if len(@a)>0 begin set @a = left(@a,len(@a)-1) exec(' select '+@a) end drop table #tt
declare @tb table(name varchar(10),counter int) insert @tb values('a',1) insert @tb values('b',2) insert @tb values('c',3)--查询 select top 1 * from @tb a join @tb b on b.name<>a.name and b.counter<>a.counter join @tb c on c.name<>a.name and c.counter<>a.counter and c.name<>b.name and c.counter<>b.counter --结果 /*name counter name counter name counter ---------- ----------- ---------- ----------- ---------- ----------- a 1 c 3 b 2(所影响的行数为 1 行) */
create table A ( name varchar(10), counter int) insert A select 'a',1 union select 'b',2 union select 'c',3 DECLARE @T_SQL varchar(8000) set @T_SQL='' select @T_SQL=@T_SQL+ ' (select * from A where name=''' + name +''') ' + name +',' from A set @T_SQL=left(@T_SQL,len(@T_SQL)-1) set @T_SQL='select * from ' + @T_SQL exec (@T_SQL)
create table test
(
name varchar(255) primary key,
counter int not null
)
go--插入测试数据
insert into test values('a',1)
insert into test values('b',2)
insert into test values('c',3)--查看测试数据
select * from test
--生成如何把两列的数据检索成一行算法
--输出参数@show
declare @name varchar(255),@counter int
declare @show varchar(8000)
declare @ int
set @ = 0
declare test_cursor CURSOR FOR select name,counter from test
open test_cursor
fetch next from test_cursor into @name,@counter
while @@fetch_status = 0
begin
if(@ = 0)
begin
set @show = @name + ' '+convert(varchar(255),@counter)
set @ = 1
end
else
begin
set @show = @show + ' '+@name + ' '+convert(varchar(255),@counter)
end
fetch next from test_cursor into @name,@counter
end
close test_cursor
deallocate test_cursor
select @show as showcolumn
GO--注:上述代码我只用了一个varchar显示数据,varchar最大长度只能是8000个字符,如果您的记录数>8000,您必须要用多个varchar做拼借进行一行的显示
(select '1001' as id ,'项目1' as name
union all
select '1002' ,'项目2'
union all
select '1003' ,'项目3'
union all
select '1004' ,'项目4'
union all
select '1005' ,'项目5'
union all
select '1006' ,'项目6')a
declare @a varchar(8000)
set @a = ''
select @a=@a +''''+id +''','''+ name +''',' from #tt
if len(@a)>0
begin
set @a = left(@a,len(@a)-1)
exec(' select '+@a)
end
drop table #tt
insert @tb values('a',1)
insert @tb values('b',2)
insert @tb values('c',3)--查询
select top 1 *
from @tb a
join @tb b on b.name<>a.name and b.counter<>a.counter
join @tb c on c.name<>a.name and c.counter<>a.counter
and c.name<>b.name and c.counter<>b.counter
--结果
/*name counter name counter name counter
---------- ----------- ---------- ----------- ---------- -----------
a 1 c 3 b 2(所影响的行数为 1 行)
*/
( name varchar(10),
counter int)
insert A
select 'a',1 union
select 'b',2 union
select 'c',3
DECLARE @T_SQL varchar(8000)
set @T_SQL=''
select @T_SQL=@T_SQL+ ' (select * from A where name=''' + name +''') ' + name +',' from A
set @T_SQL=left(@T_SQL,len(@T_SQL)-1)
set @T_SQL='select * from ' + @T_SQL
exec (@T_SQL)