select id,
name=(case when ColumnName='Name' then max(ColumnValue) else null end),
Sex =(case when ColumnName='Sex' then max(ColumnValue) else null end),
age =(case when ColumnName='age' then max(ColumnValue) else null end)
from 表A group by id
name=(case when ColumnName='Name' then max(ColumnValue) else null end),
Sex =(case when ColumnName='Sex' then max(ColumnValue) else null end),
age =(case when ColumnName='age' then max(ColumnValue) else null end)
from 表A group by id
,name=max(case when columnname='name' then columnvalue else '' end)
,sex=max(case when columnname='sex' then columnvalue else '' end)
,age=max(case when columnname='age' then columnvalue else '' end)
from A
group by id
sex=(select colvalue from aa where id=aa.id and columnname='sex'),
age=(select colvalue from aa where id=aa.id and columnname='age')
from (select distinct id from a) aa
sex=(select columnvalue from aa where id=aa.id and columnname='sex'),
age=(select columnvalue from aa where id=aa.id and columnname='age')
from (select distinct id from a) aa --列名少写了,加上去
SELECT id,
(SELECT ColumnValue
FROM table
where ColumnName='Name' ) AS Name,
(SELECT ColumnValue
FROM table
where ColumnName='Sex' ) AS Sex,
(SELECT ColumnValue
FROM table
where ColumnName='age' ) AS age
FROM table
GROUP BY id
insert into A
select '1','Name', 'Peter'
union select '1','Sex', 'male'
union select '1','age' ,'17'union select '2','Name' ,'Tom'
union select '2','Sex','male'
union select '2','age','18'union select '3','Name','David'
union select '3' ,'sex','male'
union select '3','age','9'
select * from A order by id--测试
declare @sql varchar(8000);
select @sql = 'select id '
select @sql = @sql+',isnull(max(case when columnname = '''+columnname +'''then columnvalue end),'''') ['+columnname+']'
from (select distinct columnname from A ) B
select @sql = @sql +' from A group by id'
print @sql
exec( @sql)/*测试结果*/
id age name Sex
1 17 Peter male
2 18 Tom male
3 9 David male