SELECT ID ,MAX(CASE WHEN type='CN' THEN Keyword END) 'Keyword_CN' ,MAX(CASE WHEN type='EN' THEN Keyword END) 'Keyword_EN' ,MAX(CASE WHEN type='PY' THEN Keyword END) 'Keyword_PY' FROM TableName GROUP BY ID
--> 测试数据:[TB] if object_id('[TB]') is not null drop table [TB] create table [TB]([ID] varchar(2),[Keyword] varchar(5),[type] varchar(2)) insert [TB] select '01','汽车','CN' union all select '01','car','EN' union all select '01','QiChe','PY'select * from [TB] select ID, max(case [type] when 'CN' then [Keyword] else '' end) [KeyWord_CN], max(case [type] when 'EN' then [Keyword] else '' end) [KeyWord_EN], max(case [type] when 'PY' then [Keyword] else '' end) [KeyWord_PY] from tb group by ID/*ID KeyWord_CN KeyWord_EN KeyWord_PY ---- ---------- ---------- ---------- 01 汽车 car QiChe(1 行受影响)*/
if OBJECT_ID('tb') is not null drop table tb go create table tb (ID varchar(2),Keyword varchar(5),type varchar(4)) insert into tb select '01', '汽车', 'CN' union all select '01', 'car' ,'EN' union all select '01', 'QiChe', 'PY' declare @sql varchar(8000) set @sql='' select @sql=@sql+', Keyword_'+type+' = max(case type when '''+type+''' then keyword else null end)' from tb group by type exec('select ID '+@sql+' from tb group by ID')ID Keyword_CN Keyword_EN Keyword_PY 01 汽车 car QiChe
case when
,MAX(CASE WHEN type='CN' THEN Keyword END) 'Keyword_CN'
,MAX(CASE WHEN type='EN' THEN Keyword END) 'Keyword_EN'
,MAX(CASE WHEN type='PY' THEN Keyword END) 'Keyword_PY'
FROM TableName GROUP BY ID
if object_id('[TB]') is not null drop table [TB]
create table [TB]([ID] varchar(2),[Keyword] varchar(5),[type] varchar(2))
insert [TB]
select '01','汽车','CN' union all
select '01','car','EN' union all
select '01','QiChe','PY'select * from [TB]
select ID,
max(case [type] when 'CN' then [Keyword] else '' end) [KeyWord_CN],
max(case [type] when 'EN' then [Keyword] else '' end) [KeyWord_EN],
max(case [type] when 'PY' then [Keyword] else '' end) [KeyWord_PY]
from tb
group by ID/*ID KeyWord_CN KeyWord_EN KeyWord_PY
---- ---------- ---------- ----------
01 汽车 car QiChe(1 行受影响)*/
if OBJECT_ID('tb') is not null
drop table tb
go
create table tb (ID varchar(2),Keyword varchar(5),type varchar(4))
insert into tb
select '01', '汽车', 'CN' union all
select '01', 'car' ,'EN' union all
select '01', 'QiChe', 'PY'
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+', Keyword_'+type+' = max(case type when '''+type+''' then keyword else null end)'
from tb
group by type
exec('select ID '+@sql+' from tb group by ID')ID Keyword_CN Keyword_EN Keyword_PY
01 汽车 car QiChe