ms2000下行列转换。

原始表为：

转换后表：

drop table #tmpBGFZMC;
drop table #tmpITEM
CREATE TABLE #tmpBGFZMC
(
[F_ID] [int] IDENTITY (1, 1) NOT NULL ,
[F_ZL] [varchar](30) COLLATE Chinese_PRC_CI_AS NULL ,
[F_BGFZMC] [varchar](300) COLLATE Chinese_PRC_CI_AS NULL
);
CREATE TABLE #tmpITEM
( [F_ID] [int] IDENTITY (1, 1) NOT NULL ,
[F_FXYP] [varchar](30) COLLATE Chinese_PRC_CI_AS NULL ,
[F_FXFF] [varchar](30) COLLATE Chinese_PRC_CI_AS NULL ,
[F_FXXM] [int] NULL , [F_PD] [int] NULL
)
---
insert into #tmpBGFZMC (F_ZL, F_BGFZMC) select distinct t2.F_ZL, LTrim(RTrim(t2.F_BGFZMC)) As BGFZMC from Result As t1 Left Join ANALYST_ITEM As t2 On t2.F_FXXMBH = t1.F_FXXM Where t1.F_YSJLBH = '123' Order By t2.F_ZL, BGFZMC
---
insert into #tmpITEM (F_FXYP, F_FXFF, F_FXXM, F_PD) select t1.F_FXYP, t1.F_FXFF, t1.F_FXXM, max(t2.F_PD) as pd from Result t1 left join MethItem_SubMethItem As t2 On (t2.F_FXFF = '167.1') and (t2.F_ZFXXM = t1.F_FXXM) where (t1.F_YSJLBH = '123') and (t1.F_ItemType = '52001') group by t1.F_FXYP, t1.F_FXFF, t1.F_FXXM order by t1.F_FXYP, pd desc, t1.F_FXXM

---
---
select distinct t2.F_ID as F_Row, t3.F_ID as F_Col, t1.* from vResultByItem as t1 left join (select distinct t4.*, t5.F_ZFXXM from #tmpITEM as t4 left join MethItem_SubMethItem as t5 on (t5.F_FXXM = t4.F_FXXM) and (t5.F_FXFF = '167.1') and (t5.F_PD < 2) where not exists (select 1 from MethItem_SubMethItem t6 where (t5.F_FXFF = t6.F_FXFF) and (t5.F_ZFXXM = t6.F_FXXM) and (t5.F_PD <> 0))) as t2 on (t2.F_FXYP = t1.F_FXYP) and ((t2.F_ZFXXM = t1.F_FXXM) or (t2.F_FXXM = t1.F_FXXM)) left join #tmpBGFZMC t3 on t3.F_BGFZMC = t1.F_BGFZMC where (t1.F_YSJLBH = '123') and (not t2.F_ID is null) order by t1.F_FXYP, t2.F_ID, t3.F_ID
---


declare @sqlold varchar(8000)
set @sqlold = 'select F_Row,F_YPBHOne,F_YSJLBH,F_FXYP,F_YPYH,max(case F_ItemType when ''52001'' then F_FXXMMC else '''' end) [分析项目]'
select @sqlold = @sqlold + ' , max(case F_BGFZMC when ''' + F_BGFZMC + ''' then '+convert(varchar(200),'F_YSJG')+' else '''' end) [' + F_BGFZMC + ']'
from (select top 1000 F_BGFZMC from (
select top 1000  t2.F_ID as F_Row, t3.F_ID as F_Col, t1.F_BGFZMC,t1.F_YSJG,t1.Result_ID,t1.F_YPBHOne,t1.F_YSJLBH,t1.F_FXYP,t1.F_YPYH,t1.F_FXXMMC,t1.F_itemType from vResultByItem as t1 left join (select distinct t4.*, t5.F_ZFXXM from #tmpITEM as t4 left join MethItem_SubMethItem as t5 on (t5.F_FXXM = t4.F_FXXM) and (t5.F_FXFF = '167.1') and (t5.F_PD < 2) where not exists (select 1 from MethItem_SubMethItem t6 where (t5.F_FXFF = t6.F_FXFF) and (t5.F_ZFXXM = t6.F_FXXM) and (t5.F_PD <> 0))) as t2 on (t2.F_FXYP = t1.F_FXYP) and ((t2.F_ZFXXM = t1.F_FXXM) or (t2.F_FXXM = t1.F_FXXM)) left join #tmpBGFZMC t3 on t3.F_BGFZMC = t1.F_BGFZMC where (t1.F_YSJLBH = '123') and (not t2.F_ID is null) group by t2.F_ID , t3.F_ID , t1.F_BGFZMC,t1.F_YSJG,t1.Result_ID,t1.F_YPBHOne,t1.F_YSJLBH,t1.F_FXYP,t1.F_YPYH,t1.F_FXXMMC,t1.F_itemType
)as tb where Result_ID=(select min(Result_ID) from (
select top 1000  t2.F_ID as F_Row, t3.F_ID as F_Col, t1.F_BGFZMC,t1.F_YSJG,t1.Result_ID,t1.F_YPBHOne,t1.F_YSJLBH,t1.F_FXYP,t1.F_YPYH,t1.F_FXXMMC,t1.F_itemType from vResultByItem as t1 left join (select distinct t4.*, t5.F_ZFXXM from #tmpITEM as t4 left join MethItem_SubMethItem as t5 on (t5.F_FXXM = t4.F_FXXM) and (t5.F_FXFF = '167.1') and (t5.F_PD < 2) where not exists (select 1 from MethItem_SubMethItem t6 where (t5.F_FXFF = t6.F_FXFF) and (t5.F_ZFXXM = t6.F_FXXM) and (t5.F_PD <> 0))) as t2 on (t2.F_FXYP = t1.F_FXYP) and ((t2.F_ZFXXM = t1.F_FXXM) or (t2.F_FXXM = t1.F_FXXM)) left join #tmpBGFZMC t3 on t3.F_BGFZMC = t1.F_BGFZMC where (t1.F_YSJLBH = '123') and (not t2.F_ID is null) group by t2.F_ID , t3.F_ID , t1.F_BGFZMC,t1.F_YSJG,t1.Result_ID,t1.F_YPBHOne,t1.F_YSJLBH,t1.F_FXYP,t1.F_YPYH,t1.F_FXXMMC,t1.F_itemType
Order By  t3.F_ID
)as tC where F_BGFZMC=tb.F_BGFZMC)
order by F_Col) as a
set @sqlold = @sqlold + ' from (
select top 1000  t2.F_ID as F_Row, t3.F_ID as F_Col, t1.F_BGFZMC,t1.F_YSJG,t1.Result_ID,t1.F_YPBHOne,t1.F_YSJLBH,t1.F_FXYP,t1.F_YPYH,t1.F_FXXMMC,t1.F_itemType from vResultByItem as t1 left join (select distinct t4.*, t5.F_ZFXXM from #tmpITEM as t4 left join MethItem_SubMethItem as t5 on (t5.F_FXXM = t4.F_FXXM) and (t5.F_FXFF = ''167.1'') and (t5.F_PD < 2) where not exists (select 1 from MethItem_SubMethItem t6 where (t5.F_FXFF = t6.F_FXFF) and (t5.F_ZFXXM = t6.F_FXXM) and (t5.F_PD <> 0))) as t2 on (t2.F_FXYP = t1.F_FXYP) and ((t2.F_ZFXXM = t1.F_FXXM) or (t2.F_FXXM = t1.F_FXXM)) left join #tmpBGFZMC t3 on t3.F_BGFZMC = t1.F_BGFZMC where (t1.F_YSJLBH = ''123'') and (not t2.F_ID is null) group by t2.F_ID , t3.F_ID , t1.F_BGFZMC,t1.F_YSJG,t1.Result_ID,t1.F_YPBHOne,t1.F_YSJLBH,t1.F_FXYP,t1.F_YPYH,t1.F_FXXMMC,t1.F_itemType
Order By  t3.F_ID
)as tb group by F_Row,F_YPBHOne,F_YSJLBH,F_FXYP,F_YPYH'
exec(@sqlold)

 

 

 

2.create table cj  --创建表cj
(
    ID       Int IDENTITY (1,1)     not null, --创建列ID,并且每次新增一条记录就会加1
    Name     Varchar(50),  
    Subject  Varchar(50),
    Result   Int, 
    primary key (ID)      --定义ID为表cj的主键     
);
--Truncate table cj

Insert into cj
Select '张三','语文',80 union all
Select '张三','数学',90 union all
Select '张三','物理',85 union all
Select '李四','语文',85 union all
Select '李四','物理',82 union all
Select '李四','英语',90 union all
Select '李四','政治',70 union all
Select '王五','英语',90
Select * from cj
--行列转换
Declare @sql varchar(8000)
Set @sql = 'Select Name as 姓名'
Select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Result else 0 end) ['+Subject+']'
from (select distinct Subject from cj) as cjq  --把所有唯一的科目的名称都列举出来
Select @sql = @sql+' from cj group by name'
Exec (@sql)