--建表 create table dbo.orders ( orderid int not null primary key nonclustered, orderdate datetime not null, empid int not null, custid varchar(5) not null, qty int not null) --插入数据 insert into orders values(30001,'20020802',3,'a',10) insert into orders values(10001,'20021024',1,'a',12) insert into orders values(10005,'20021224',1,'b',20) insert into orders values(40001,'20030109',4,'a',40) insert into orders values(10006,'20030118',1,'c',14) insert into orders values(20001,'20030212',2,'b',12) insert into orders values(40005,'20040212',4,'a',10) insert into orders values(20002,'20040216',2,'c',20) insert into orders values(30003,'20040418',3,'b',15) insert into orders values(30004,'20020418',3,'c',22) insert into orders values(30007,'20020907',3,'d',30) --数据。 select custid,year(orderdate) as orderdate ,qty from orders order by custid custid orderdate qty ------ ----------- ----------- a 2002 10 a 2002 12 a 2003 40 a 2004 10 b 2004 15 b 2003 12 b 2002 20 c 2002 22 c 2003 14 c 2004 20 d 2002 30 (11 行受影响) --转阿转。 select custid,sum(case when orderdate=2002 then qty end) as [2002], sum(case when orderdate=2003 then qty end) as [2003], sum(case when orderdate=2004 then qty end) as [2004] from ( select custid,year(orderdate) as orderdate ,qty from orders ) as d group by custid; custid 2002 2003 2004 ------ ----------- ----------- ----------- a 22 40 10 b 20 12 15 c 22 14 20 d 30 NULL NULL 警告: 聚合或其他 SET 操作消除了空值。 (4 行受影响) --行转列 select * from (select custid,year(orderdate) as orderyear,qty from dbo.orders) as d pivot (sum(qty) for orderyear in ([2002],[2003],[2004])) as p; custid 2002 2003 2004 ------ ----------- ----------- ----------- a 22 40 10 b 20 12 15 c 22 14 20 d 30 NULL NULL (4 行受影响) --反转。 select custid,orderyear,qty from pvtt unpivot ( qty for orderyear in ([2002],[2003],[2004])) as u; custid orderyear qty ------ -------------------------------------------------------------------------------------------------------------------------------- ----------- a 2002 22 a 2003 40 a 2004 10 b 2002 20 b 2003 12 b 2004 15 c 2002 22 c 2003 14 c 2004 20 d 2002 30 (10 行受影响) --动态反转。注意@b变量要放在括号里，否则报不是有效的标识符 declare @a varchar(4000),@b varchar(4000) set @a='' select @a=@a+'],['+cast(a as varchar(10)) from (select distinct year(orderdate) as a from orders) as b set @a=right(@a,len(@a)-2)+']' set @b='select custid,orderyear,qty from pvtt unpivot (qty for orderyear in ('+ @a +')) as c' exec (@b) custid orderyear qty ------ ---------------------------------------------------------------------------------------- a 2002 22 a 2003 40 a 2004 10 b 2002 20 b 2003 12 b 2004 15 c 2002 22 c 2003 14 c 2004 20 d 2002 30 (10 行受影响) --动态反转,注意DISTINCT引用否则则提示多次引用列。 declare @a varchar(4000),@b varchar(4000) set @a='' select @a=@a+aa from (select distinct (select '['+cast ( a as varchar(10))+'],' as [text()] from (select distinct year(orderdate) as a from orders ) as a3 for xml path('')) as aa from (select distinct year(orderdate) as a from orders ) as a2) as c set @a=left(@a,len(@a)-1) set @b='select custid,orderyear,qty from pvtt unpivot (qty for orderyear in ('+ @a +')) as c' exec (@b) custid orderyear qty ------ -------------------------------------------------------------------------------------- a 2002 22 a 2003 40 a 2004 10 b 2002 20 b 2003 12 b 2004 15 c 2002 22 c 2003 14 c 2004 20 d 2002 30 (10 行受影响) --通过系统视图（INFORMAT_SCHEMA.COLUMNS）拉列表。 declare @a as table( y int not null primary key ); declare @cols as nvarchar(max) declare @sql as nvarchar(max) set @cols=stuff( (select N','+quotename(y) as [text()] from (select column_name as y from INFORMATION_SCHEMA.COLUMNS where table_schema=N'dbo' and table_name=N'pvtt' and column_name not in (N'custid')) as y order by y for xml path('')),1 ,1 ,N''); set @sql=N'select custid,orderyear,qty from dbo.pvtt unpivot(qty for orderyear in ('+@cols+N')) as u;'; exec sp_executesql @sql custid orderyear qty ------ ------------------------------------------------------------------------------------- a 2002 22 a 2003 40 a 2004 10 b 2002 20 b 2003 12 b 2004 15 c 2002 22 c 2003 14 c 2004 20 d 2002 30 (10 行受影响)