行 转 列

第一种：

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;