请教一下这个sql语句的问题

Sheldon_Lou 2011-06-01 06:12:03

有一张表 "tabel" 4个字段
结构是
idex(int自动增长),
id,
name1,varchar(20)
name2,varchar(20)

有若干条记录,
值分别是
1 001 a1 b1
2 001 a2 b2
3 001 a3 b3
4 001 a4 b4

对于这四条数据
我想把他正整合成一条
格式是
1 001 a1 b1 a2 b2 a3 b3 a4 b4

请问这sql语句应该怎么写?

...全文

73 6 打赏收藏转发到动态举报

写回复

用AI写文章

6 条回复

切换为时间正序

请发表友善的回复…

发表回复

liang145 2011-06-01

打赏
举报



create table #tb

(idex int identity(1,1),

id varchar(20),

name1 varchar(20),

name2 varchar(20))



insert #tb

select '001', 'a1', 'b1' union all

select '001', 'a2', 'b2' union all

select '001', 'a3', 'b3' union all

select '001', 'a4', 'b4' union all

select '002', 'yt', 'er' union all

select '002', 'fg', 'gf' union all

select '002', 'bv', 'hgf' union all

select '003', 'a43', 'b43'



declare @Lid as nvarchar(10)

declare @sql as nvarchar(4000)

select top 1 @Lid=id from #tb group by id order by count(1) desc

set @sql=''

;with T as (select Row_Number() over(partition by id order by idex) as gid,* from #tb)

select @sql=@sql+',max(case when gid='''+ltrim(gid)+''' then name1 else '''' end) as name'+ltrim(gid)

                +',max(case when gid='''+ltrim(gid)+''' then name2 else '''' end) as name'+ltrim(gid)+'2'

                from T where id=@Lid

set @sql='select min(idex) as idex,id'+@sql+' from '+

         '(select Row_Number() over(partition by id order by idex) as gid,* from #tb) as T group by id'

exec(@sql)







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

------------------还是这样？

select min(idex) as idex,id,[name]=(select name1+' '+name2 from #tb as t where t.id=t1.id for xml path(''))  

     from #tb as t1 group by id