行列转化

use pubs
drop table t
drop table e

--行转列
create   table   t
(
      Name         varchar(10)   ,
      Subject   varchar(10)   ,
      Result     int
)

insert   into   t(Name   ,   Subject   ,   Result)   values( '张三 ', '语文 ', '73 ')
insert   into   t(Name   ,   Subject   ,   Result)   values( '张三 ', '数学 ', '83 ')
insert   into   t(Name   ,   Subject   ,   Result)   values( '张三 ', '物理 ', '93 ')
insert   into   t(Name   ,   Subject   ,   Result)   values( '李四 ', '语文 ', '74 ')
insert   into   t(Name   ,   Subject   ,   Result)   values( '李四 ', '数学 ', '83 ')
insert   into   t(Name   ,   Subject   ,   Result)   values( '李四 ', '物理 ', '93 ')

--不固定
 declare   @s   varchar(8000)  
  set   @s=''  
  select   @s=@s+',['+Subject+']=sum(case   Subject   when   '''+Subject+'''   then   Result   else   0   end)'  
  from   t  
  group   by   Subject  
  exec('select   姓名=Name'+@s+' ,合计=sum(Result)  
  from   #t  
  group   by   Name')  
  go
select * from t
--固定
select 姓名=Name,语文=isnull(sum(case Subject when '语文' then Result end),0),
    数学=isnull(sum(case Subject when '数学' then Result end),0),
    物理=isnull(sum(case Subject when '物理' then Result end),0),
    合计=isnull(sum(Result),0)
    from t
    group by Name
    order by Name

 

 

--列转行
create   table   e
(
      姓名   varchar(10)   ,
      语文   int   ,
      数学   int   ,
      物理   int
)

insert   into   e(姓名   ,   语文   ,   数学   ,   物理)   values( '张三 ',73,83,93)
insert   into   e(姓名   ,   语文   ,   数学   ,   物理)   values( '李四 ',74,84,94)
select * from e
select   姓名   as   Name, '语文 '   as   Subject,语文   as   Result   from   e   union
select   姓名   as   Name, '数学 '   as   Subject,数学   as   Result   from   e   union
select   姓名   as   Name, '物理 '   as   Subject,物理   as   Result   from   e
order   by   姓名   desc  

--其他
drop table test,T1
create table test([id] varchar(4), amount int, style varchar(20))

insert into test values('A1',500,'重不良')
insert into test values('A1',300,'中不良')
insert into test values('A1',150,'轻不良')
insert into test values('B1',300,'重不良')
insert into test values('B1',200,'轻不良')
insert into test values('B2',120,'轻不良')
select * from test
declare @sql varchar(8000)
set @sql='select [id], '
select @sql=@sql+quotename(style)+'=max(case when style='+quotename(style, '''')+' then rtrim(amount) else ''-'' end),' from test group by style
select @sql=left(@sql, len(@sql)-1), @sql=@sql+' from test group by [id]'
exec(@sql)

create table T1(名字 varchar(10), 科目 varchar(10), 分数 int)
insert T1 select '1', '语文', 90
union all select '1', '数学', 80
union all select '2', '语文', 90
union all select '2', '数学', 85
union all select '2', '外语', 70

select * from T1
declare @sql varchar(8000)
declare @sql2 varchar(1000)
set @sql=''
set @sql2='總分='
select @sql=@sql+quotename(科目)+'=isnull( max(case when 科目='+quotename(科目, '''')+' then 分数 end), 100),',
@sql2=@sql2+'tmp.'+科目+'+'
from T1
group by 科目
select
@sql2=left(@sql2, len(@sql2)-1),
@sql=left(@sql, len(@sql)-1),
@sql='select tmp.*,'+@sql2+' from (select 名字,'+@sql,
@sql=@sql+' from T1 group by 名字) tmp'
exec(@sql)

 

--其他完整例子
--方法一(L字段数目不确定)
drop table tb
CREATE TABLE tb(sn varchar(20),process_id int,data_id int,measuredata numeric(9,2))
INSERT tb SELECT '12770006',1,1,1.34
UNION ALL SELECT '12770006',1,2,046
UNION ALL SELECT '12770006',1,3,9.82
UNION ALL SELECT '14061916',2,1,5.5
UNION ALL SELECT '14061916',2,2,4.36
UNION ALL SELECT '12770006',2,1,6.43
UNION ALL SELECT '12770006',2,2,0.12
UNION ALL SELECT '12770006',2,3,6.37
UNION ALL SELECT '14061916',3,1,3.4
UNION ALL SELECT '14061916',3,2,1.1
select * from tb
--查询处理
DECLARE @s nvarchar(4000)
--交叉报表处理代码头
SET @s='SELECT sn,process_id'
--生成列记录水平显示的处理代码拼接(处理Item列)
SELECT @s=@s
+','+QUOTENAME(data_id)
+N'=SUM(CASE data_id WHEN '+QUOTENAME(data_id,N'''')
+N' THEN measuredata END)'
FROM tb
GROUP BY data_id
 
--拼接交叉报表处理尾部,并且执行拼接后的动态SQL语句
EXEC(@s+N'
FROM tb
GROUP BY sn,process_id')
 
--方法二(L字段数目不确定)
declare @a varchar(2000)
set @a='select sn,process_id'
select @a=@a+',field'+ltrim(tb.data_id)+'=sum(case when tb.data_id='''+ltrim(tb.data_id)+''' then tb.measuredata else 0 end)'
from tb
group by tb.data_id
set @a=@a+' from tb group by sn,process_id'
exec(@a)
 
--方法三(L字段数目固定)
Select a.SN,a.Process_id,
      [field1]=max(case when data_id=1 then measuredata end),
      [field2]=max(case when data_id=2 then measuredata end),
      [field3]=max(case when data_id=3 then measuredata end),
      [field4]=max(case when data_id=4 then measuredata end)
From tb a
Group By a.SN,a.Process_id
order by a.SN,a.Process_id