在一个A表中有如下数据ID title Text
1 taa aa
1 tbb bb
1 tcc cc
2 tdd dd
2 tee ee
2 tff ff我想实现如下新表
ID taa tbb tcc
1 aa bb cc
2 dd ee ff
是否可以实现?如何实现?
1 taa aa
1 tbb bb
1 tcc cc
2 tdd dd
2 tee ee
2 tff ff我想实现如下新表
ID taa tbb tcc
1 aa bb cc
2 dd ee ff
是否可以实现?如何实现?
1 taa aa
1 tbb bb
1 tcc cc
2 taa dd
2 tbb ee
2 tcc ff 我想实现如下新表
ID taa tbb tcc
1 aa bb cc
2 dd ee ff
是否可以实现?如何实现?
id,
taa=max(case when title='taa' then text end),
tbb=max(case when title='tbb' then text end),
tcc=max(case when title='tcc' then text end)
from [A]
group by id
declare @T table(ID int,title varchar(3),Text varchar(2))
insert @T
select 1,'taa','aa' union all
select 1,'tbb','bb' union all
select 1,'tcc','cc' union all
select 2,'taa','dd' union all
select 2,'tbb','ee' union all
select 2,'tcc','ff'select ID,
taa=max(case title when 'taa' then Text end),
tbb=max(case title when 'tbb' then Text end),
tcc=max(case title when 'tcc' then Text end)
from @T group by ID/*
ID taa tbb tcc
1 aa bb cc
2 dd ee ff
*/
declare @sql varchar(8000)
set @sql = 'select id'
select @sql = @sql + ' , max(case title when ''' + title + ''' then Text else '' '' end) [' + title + ']'
from (select distinct title from tb) as a
set @sql = @sql + ' from tb group by id'
exec(@sql)
declare @a table(id int,title varchar(20), text varchar(20))
insert @a
select 1,'taa','aa'
union all
select 1,'tbb','bb'
union all
select 1,'tcc','cc'
union all
select 1,'taa','dd'
union all
select 1,'tbb','ee'
union all
select 1,'tcc','ff'
select id,taa=max(case when title='taa' then text end),
tbb=max(case when title='tbb' then text end),
tcc=max(case when title='tcc' then text end)
from @a
group by id
/*
(所影响的行数为 6 行)id taa tbb tcc
----------- -------------------- -------------------- --------------------
1 dd ee ff(所影响的行数为 1 行)警告: 聚合或其它 SET 操作消除了空值。
*/
这是楼上的结果
create table #T(ID int,title varchar(3),Text varchar(2))
insert #T
select 1,'taa','aa' union all
select 1,'tbb','bb' union all
select 1,'tcc','cc' union all
select 2,'taa','dd' union all
select 2,'tbb','ee' union all
select 2,'tcc','ff'--动态SQL
declare @sql varchar(8000)
select @sql=coalesce(@sql+',','select ID,')+'['+title+']=max(case title when '''+title+''' then Text end)' from #T group by Title
set @sql=@sql+' from #T group by ID'
exec(@sql)/*
ID taa tbb tcc
1 aa bb cc
2 dd ee ff
*/--删除测试
drop table #T
insert into tb values(1, 'taa', 'aa')
insert into tb values(1, 'tbb', 'bb')
insert into tb values(1, 'tcc', 'cc')
insert into tb values(2, 'taa', 'dd')
insert into tb values(2, 'tbb', 'ee')
insert into tb values(2, 'tcc', 'ff')
go
declare @sql varchar(8000)
set @sql = 'select id'
select @sql = @sql + ' , max(case title when ''' + title + ''' then Text else '' '' end) [' + title + ']'
from (select distinct title from tb) as a
set @sql = @sql + ' from tb group by id'
exec(@sql) drop table tb/*
id taa tbb tcc
----------- ---------- ---------- ----------
1 aa bb cc
2 dd ee ff
*/
insert a select 1 ,'taa', 'aa'
union all select 1 ,'tbb' ,'bb'
union all select 1 ,'tcc' ,'cc'
union all select 2 ,'taa' ,'dd'
union all select 2 ,'tbb' ,'ee'
union all select 2 ,'tcc' ,'ff'
declare @sql varchar(1000)
set @sql='select id,'
select @sql=@sql+' max(case when title='''+title+''' then text end) ['+title+'],' from a group by title order by title
select @sql=left(@sql,len(@sql)-1)
exec(@sql+' from a group by id')
/*
id taa tbb tcc
----------- ---------- ---------- ----------
1 aa bb cc
2 dd ee ff
*/
select @sql='select id'
select @sql=@sql+', '+title+'= max(case when title='''+title+''' then [text] end)' from (select distinct title from a) a
select @sql=@sql+' from a group by id'
exec(@sql)NND 回复半天出错
郁闷死