第一种：
CREATE TABLE #T1(C1 VARCHAR(10),C2 VARCHAR(20),C3 VARCHAR(10))
INSERT INTO #T1(C1,C2,C3) VALUES('1','DCP','5')
INSERT INTO #T1(C1,C2,C3) VALUES('2','PCB','8')
INSERT INTO #T1(C1,C2,C3) VALUES('6','ECD','9')
INSERT INTO #T1(C1,C2,C3) VALUES('6','PCB','2')
INSERT INTO #T1(C1,C2,C3) VALUES('6','PCB','3')
SELECT C2,[1] AS [1],[2] AS [2],[6] AS [6] FROM (
SELECT C1,C2,C3 FROM #T1) AS SourceTemp1
PIVOT(
MAX(C3) FOR C1 IN([1],[2],[6])) AS PIVOTTemp1
DROP TABLE #T1
第二种：
create table #tbcrew
(
GroupID int,
LevelName nvarchar(50) collate SQL_Latin1_General_CP1_CI_AS,
CrewNameList int
)
declare @sql nvarchar(max);
insert into #tbcrew(GroupID,LevelName,CrewNameList) values(1,'m',1);
insert into #tbcrew(GroupID,LevelName,CrewNameList) values(1,'v',1);
insert into #tbcrew(GroupID,LevelName,CrewNameList) values(2,'v',2);
insert into #tbcrew(GroupID,LevelName,CrewNameList) values(3,'s',2);
select * from #tbcrew;
set @sql=N'select GroupID';
select @sql=@sql+N',sum(case LevelName when '''+LevelName+''' then CrewNameList end) ['+LevelName+']'
from (select distinct LevelName from #tbcrew) as a
set @sql=@sql+' from #tbcrew group by GroupID';
exec sp_executesql @sql;
drop table #tbcrew;