请SQL语句,不要存储过程,行转例并合半值-在线等,马上结贴!

[upldel]

NORNO
1
1
1
1
0
0
0
0
1
1
0
0
0
0
0
1
得到结果111100001100001(结果为VARCHAR类型)

[-晴天]

create table tb(NORNO int)
insert into tb select 1
insert into tb select 1
insert into tb select 1
insert into tb select 1
insert into tb select 0
insert into tb select 0
insert into tb select 0
insert into tb select 0
insert into tb select 1
insert into tb select 1
insert into tb select 0
insert into tb select 0
insert into tb select 0
insert into tb select 0
insert into tb select 0
insert into tb select 1
go
declare @c varchar(30)
select @c=isnull(@c,'')+ltrim(norno) from tb
select @c
/*
------------------------------
1111000011000001

(1 行受影响)
*/
go
drop table tb

[AcHerat]

declare @str varchar(8000)
select @str = isnull(@str,'')+ltrim(NORNO
) from tb
select @str

[AcHerat]

好吧！晴天真快，还带测试的。。。

[--小F--]

----------------------------------------------------
/*如何将一列中所有的值一行显示
数据源
  a
  b
  c
  d
  e
结果
a,b,c,d,e
*/

create table tb(col varchar(20))
insert tb values ('a')
insert tb values ('b')
insert tb values ('c')
insert tb values ('d')
insert tb values ('e')
go

--方法一
declare @sql varchar(1000)
set @sql = ''
select @sql = @sql + t.col + ',' from (select col from tb) as t
set @sql='select result = ''' + left(@sql , len(@sql) - 1) + ''''
exec(@sql)
/*
result     
---------- 
a,b,c,d,e,
*/

--方法二
declare @output varchar(8000)
select @output = coalesce(@output + ',' , '') + col from tb
print @output
/*
a,b,c,d,e
*/

---方法三
declare @s varchar(1000)
select @s=isnull(@s+',' , '')+col from tb
select @s
/*
a,b,c,d,e
*/

drop table tb


-------------

[-晴天]

[Quote=引用 3 楼 acherat 的回复:]
好吧！晴天真快，还带测试的。。。
[/Quote]

帖子出来都4分半钟了...

[upldel]

declare @c varchar(30)
select @c=isnull(@c,'')+ltrim(norno) from tb
select @c

@C什么意思,看不懂,能不能直接写成 select isnull('')+ltrim(norno) from tb,不要管数成类型了,

[AcHerat]

不行的，@c是一个变量，存储你最后那种形式的结果的，不然不是结果那种形式。

[-晴天]

[Quote=引用 6 楼 upldel 的回复:]
SQL code
declare @c varchar(30)
select @c=isnull(@c,'')+ltrim(norno) from tb
select @c

@C什么意思,看不懂,能不能直接写成 select isnull('')+ltrim(norno) from tb,不要管数成类型了,
[/Quote]

要通过一个中间变量来进行累加.@c 就是这个中间变量.

[cd731107]

select @c=isnull(@c,'')+ltrim(norno) from tb
这是一个赋值语句，用来合并
select isnull('')+ltrim(norno) from tb
这不是一个赋值语句，所在列不能合并的

[-晴天]

create table tb1(级别 int,NOIS int,工序 varchar(10))
insert into tb1 select 1,1,'demo1'
insert into tb1 select 2,1,'demo1'
insert into tb1 select 2,1,'demo1'
insert into tb1 select 2,0,'demo1'
insert into tb1 select 2,0,'demo1'
insert into tb1 select 2,1,'demo1'
insert into tb1 select 2,1,'demo1'
insert into tb1 select 2,1,'demo1'
insert into tb1 select 2,1,'demo1'
insert into tb1 select 2,0,'demo2'
insert into tb1 select 2,0,'demo2'
insert into tb1 select 2,0,'demo2'
insert into tb1 select 2,1,'demo2'
insert into tb1 select 2,1,'demo2'
insert into tb1 select 2,1,'demo2'
insert into tb1 select 2,1,'demo2'
insert into tb1 select 1,1,'demo2'
insert into tb1 select 2,1,'demo2'
insert into tb1 select 2,1,'demo2'
insert into tb1 select 2,1,'demo2'
create table tb2(工序 varchar(10),NOIS varchar(20))
insert into tb2 select 'demo1',''
insert into tb2 select 'demo2',''
go
update t 
set NOIS=(select ''+ltrim(NOIS) from tb1 where 工序=t.工序 and 级别=2 for xml path(''))
from tb2 t
select * from tb2
/*
工序         NOIS
---------- --------------------
demo1      11001111
demo2      0001111111

(2 行受影响)

*/
go
drop table tb1,tb2