把数据库中的所有记录以一个特定格式的字符显示出来的一个例子

create table tb_other
(
  f_djhmvarchar(17)not null,--单据号码
  f_tablefield varchar(20)            not null,                       --字段
  f_value      varchar(1024)                                   not null,                       --值
)

insert into tb_other select 'd001' as f_djhm,'f_a' as f_tablefield,'aaaaaaaaaaaa' as f_value union all

select 'd001' as f_djhm,'f_b' as f_tablefield,'aaaabbba' as f_value union all
select 'd001' as f_djhm,'f_c' as f_tablefield,'aacccaaaaa' as f_value union all
select 'd001' as f_djhm,'f_d' as f_tablefield,'addddaaaaaaa' as f_value

select ''''+f_value+''' as '+f_tablefield+','  as f_sql from tb_other 得到的结果为
'aaaaaaaaaaaa' as f_a,
'aaaabbba' as f_b,
'aacccaaaaa' as f_c,
'addddaaaaaaa' as f_d,

我想得到
select 'aaaaaaaaaaaa' as f_a,'aaaabbba' as f_b,'aacccaaaaa' as f_c,'addddaaaaaaa' as f_d

这样的语句,就是说全部拼成一行,前面再加个select,最的后面逗号去掉

 

hellowork(一两清风) ( ) 信誉：100    Blog

 declare @str varchar(8000)
set @str = ''
select @str = @str + ',''' + f_value + ''' as ' + f_tablefield from tb_other
select 'select ' + stuff(@str,1,1,'')

/*结果
select 'aaaaaaaaaaaa' as f_a,'aaaabbba' as f_b,'aacccaaaaa' as f_c,'addddaaaaaaa' as f_d
*/