假如数据库表A存有数据如下:
id value
1 50
1 34
1 23
1 16
如何设计查询语句,使其id号相同的数据得到的查询结果如下:
id value1 value2 value3 value4
1 50 34 23 16
就是将多行的数据整合为单行显示。在线等高手帮忙!
id value
1 50
1 34
1 23
1 16
如何设计查询语句,使其id号相同的数据得到的查询结果如下:
id value1 value2 value3 value4
1 50 34 23 16
就是将多行的数据整合为单行显示。在线等高手帮忙!
[MSSQL的]--MySQL的不知道哦--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([id] int,[col] int)
insert [tb]
select 1,50 union all
select 1,34 union all
select 1,23 union all
select 1,16select * from [tb]
create table #test(number int primary key identity(1,1),[id] int,[col] int)
goinsert into #test(id,col)
select * from tb
go
select * from #testdeclare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + convert(varchar(20),number) from #test
set @sql = '[' + @sql + ']'
print @sql
exec ('select * from (select * from #test) a pivot (max(col) for number in (' + @sql + ')) b')
/*
id 1 2 3 4
1 50 34 23 16
*/
drop table #test
drop table tb
--表结构及记录
if object_id('[tb]') is not null drop table [tb]
create table [tb]([id] int,[col] int)
insert [tb]
select 1,50 union all
select 1,34 union all
select 1,23 union all
select 1,16
--语句
select id,replace((select cast(col as varchar(5))+',' from tb where id=a.id for xml path('')),',',' ') as col
from tb a
group by id
结果:
id col
1 50 34 23 16
就是用你的表A
select id,replace((select cast(col as varchar(5))+',' from tb where id=a.id for xml path('')),',',' ') as col
from tb a /* tb就是我这里替用你的表A*/
group by id
结果:
id col
1 50 34 23 16
大哥!这句是啥意思啊?
解释一下好么?
set @sql = 'select id '
select @sql = @sql + ' , max(case id when ''' + rtrim(id) + ''' then '+ rtrim(col)+' else 0 end) [' + rtrim(col) + ']'
from tb
set @sql = @sql + ' from tb group by id'
print(@sql)
exec(@sql)
max(case id when '1' then 50 else 0 end )[col1] ,
max(case id when '1' then 34 else 0 end )[col2] ,
max(case id when '1' then 23 else 0 end )[col3] ,
max(case id when '1' then 16 else 0 end )[col4]
from tb
group by id
from a
group by id