有關于行轉列

Question

declare @tbl table
(
bzrq nchar(8),
bzzb nvarchar(8),
bzline nvarchar(8),
bzcplb nvarchar(8),
bztype nvarchar(8),
TimeNum int,
TimeNum1 int)
insert into @tbl 
select '20080101','半成品A1','line1',' 組立前制','待排程',180,60 union all
select '20080101','半成品A1','line1','組立前制','5S', 30,60 union all
select '20080101','半成品A1','line1','組立前制','休息',60,60 union all
select '20080101','半成品A1','line1','組立前制','換部品',10,60 union all
select '20080101','半成品A1','line1','組立前制','換部品',10,60 union all
select '20080101','半成品A1','line1','組立前制','待排程',180,60 union all
select '20080101','半成品A1','line1','組立前制','休息',60,60 union all
select '20080102','半成品A1','line1','組立前制','待排程',180,60 union all
select '20080102','半成品A1','line1','組立前制','待料',15,60 union all
select '20080103','半成品A1','line1','組立前制','休息',60,60 union all
select '20080102','半成品A1','line1','組立前制','換部品',10,60 union all
select '20080102','半成品A1','line1','組立前制','休息',60,60 union all
select '20080102','半成品A1','line1','組立前制','換線',15,60 create  table #tbltmp
(
   bztype nchar(8),
   rq nchar(2),
   num int
)
insert into #tbltmp
select a.bztype,a.rq,sum(TimeNum) as num from
(
    select bztype,right(bzrq,2)as rq,TimeNum from @tbl a
) as a group by a.bztype,a.rq
declare @sql varchar(8000)
set @sql = ' select isnull(bztype,''百分比'') [原因],'
select @sql= @sql+'case when grouping(bztype)=0 then rtrim(sum(case rq when '''+rq+''' then num  else 0 end)) else ltrim(str( sum(case rq when '''+rq+''' then num  else 0 end)*100.0/1440,8,2))+''%'' end as  ['+rq+'],'
    from (select distinct rq from #tbltmp ) a
set @sql = stuff(@sql,len(@sql),1,'')
set @sql=@sql+ ',case when grouping(bztype)=0 then rtrim(sum(num)) else '''' end 合計 from #tbltmp group by bztype with rollup'
exec(@sql)
 drop table #tbltmp(10 個資料列受到影響)
原因       01           02           03           合計
-------- ------------ ------------ ------------ ------------
5S       30           0            0            30
休息       120          60           60           240
待料       0            15           0            15
待排程      360          180          0            540
換部品      20           10           0            30
換線       0            15           0            15
百分比      36.81%       19.44%       4.17%        

還要新增TimeNum1行和原因的總合，還有百分比
格式如下：
原因       01           02           03           合計
-------- ------------ ------------ ------------ ------------
5S       30           0            0            30
休息       120          60           60           240
待料       0            15           0            15
待排程      360          180          0            540
換部品      20           10           0            30
換線       0            15           0            15
合計       530           280         60            870
TimeNum1  60           60           60            180
百分比(合計/TimeNum1) 36.81%       19.44%       4.17%    600%

Answer 1

TimeNum1  60          60          60            180 这行数据是什么意思?

Answer 2

create table temp1
( 
bzrq nchar(8), 
bzzb nvarchar(8), 
bzline nvarchar(8), 
bzcplb nvarchar(8), 
bztype nvarchar(8), 
TimeNum int, 
TimeNum1 int) 
insert into temp1 
select '20080101','半成品A1','line1',' 組立前制','待排程',180,60 union all 
select '20080101','半成品A1','line1','組立前制','5S', 30,60 union all 
select '20080101','半成品A1','line1','組立前制','休息',60,60 union all 
select '20080101','半成品A1','line1','組立前制','換部品',10,60 union all 
select '20080101','半成品A1','line1','組立前制','換部品',10,60 union all 
select '20080101','半成品A1','line1','組立前制','待排程',180,60 union all 
select '20080101','半成品A1','line1','組立前制','休息',60,60 union all 
select '20080102','半成品A1','line1','組立前制','待排程',180,60 union all 
select '20080102','半成品A1','line1','組立前制','待料',15,60 union all 
select '20080103','半成品A1','line1','組立前制','休息',60,60 union all 
select '20080102','半成品A1','line1','組立前制','換部品',10,60 union all 
select '20080102','半成品A1','line1','組立前制','休息',60,60 union all 
select '20080102','半成品A1','line1','組立前制','換線',15,60 create  table #tbltmp 
( 
  bztype nchar(8), 
  rq nchar(2), 
  num int 
) 
insert into #tbltmp 
select a.bztype,a.rq,sum(TimeNum) as num from 
( 
    select bztype,right(bzrq,2)as rq,TimeNum from temp1 a 
) as a group by a.bztype,a.rq 
declare @sql varchar(8000) 
set @sql = ' select isnull(bztype,''百分比'') [原因],' 
select @sql= @sql+'case when grouping(bztype)=0 then rtrim(sum(case rq when '''+rq+''' then num  else 0 end)) else ltrim(str( sum(case rq when '''+rq+''' then num  else 0 end)*100.0/1440,8,2))+''%'' end as  ['+rq+'],' 
    from (select distinct rq from #tbltmp ) a 
set @sql = stuff(@sql,len(@sql),1,'') 
set @sql=@sql+ ',case when grouping(bztype)=0 then rtrim(sum(num)) else '''' end 合計 from #tbltmp group by bztype with rollup' set @sql=@sql+' union select ''合计'','
select @sql=@sql+'cast((select sum(num) from #tbltmp where rq='''+rq+''') as varchar),' from (select distinct rq from #tbltmp ) b
set @sql=@sql+'cast((select sum(num) from #tbltmp) as varchar)'set @sql=@sql+' union select ''TimeNum1'', '
select @sql=@sql+'cast((select top 1  TimeNum1 from temp1 where right(bzrq,2)='''+rq+''') as varchar),' from (select distinct rq from #tbltmp ) c
set @sql=@sql+'cast((select sum(TimeNum1) from (select distinct right(bzrq,2) rq,TimeNum1 from temp1) f) as varchar)'
--print @sql
set @sql='select * from  ('+@sql+') z  order by z.[原因]'
exec(@sql) 
drop table #tbltmp ,temp1/*
原因       01                             02                             03                             合計                             
-------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ 
5S       30                             0                              0                              30
TimeNum1 60                             60                             60                             180
百分比      36.81%                         19.44%                         4.17%                          
待料       0                              15                             0                              15
待排程      360                            180                            0                              540
合计       530                            280                            60                             870
換部品      20                             10                             0                              30
換線       0                              15                             0                              15
休息       120                            60                             60                             240*/

Answer 3

create table temp1
( 
bzrq nchar(8), 
bzzb nvarchar(8), 
bzline nvarchar(8), 
bzcplb nvarchar(8), 
bztype nvarchar(8), 
TimeNum int, 
TimeNum1 int) 
insert into temp1 
select '20080101','半成品A1','line1',' 組立前制','待排程',180,60 union all 
select '20080101','半成品A1','line1','組立前制','5S', 30,60 union all 
select '20080101','半成品A1','line1','組立前制','休息',60,60 union all 
select '20080101','半成品A1','line1','組立前制','換部品',10,60 union all 
select '20080101','半成品A1','line1','組立前制','換部品',10,60 union all 
select '20080101','半成品A1','line1','組立前制','待排程',180,60 union all 
select '20080101','半成品A1','line1','組立前制','休息',60,60 union all 
select '20080102','半成品A1','line1','組立前制','待排程',180,60 union all 
select '20080102','半成品A1','line1','組立前制','待料',15,60 union all 
select '20080103','半成品A1','line1','組立前制','休息',60,60 union all 
select '20080102','半成品A1','line1','組立前制','換部品',10,60 union all 
select '20080102','半成品A1','line1','組立前制','休息',60,60 union all 
select '20080102','半成品A1','line1','組立前制','換線',15,60 create  table #tbltmp 
( 
  bztype nchar(8), 
  rq nchar(2), 
  num int 
) 
insert into #tbltmp 
select a.bztype,a.rq,sum(TimeNum) as num from 
( 
    select bztype,right(bzrq,2)as rq,TimeNum from temp1 a 
) as a group by a.bztype,a.rq 
declare @sql varchar(8000) 
set @sql = ' select isnull(bztype,''百分比'') [原因],' 
select @sql= @sql+'case when grouping(bztype)=0 then rtrim(sum(case rq when '''+rq+''' then num  else 0 end)) else ltrim(str( sum(case rq when '''+rq+''' then num  else 0 end)*100.0/1440,8,2))+''%'' end as  ['+rq+'],' 
    from (select distinct rq from #tbltmp ) a 
set @sql = stuff(@sql,len(@sql),1,'') 
set @sql=@sql+ ',case when grouping(bztype)=0 then rtrim(sum(num)) else '''' end 合計 from #tbltmp group by bztype with rollup' set @sql=@sql+' union select ''合计'','
select @sql=@sql+'cast((select sum(num) from #tbltmp where rq='''+rq+''') as varchar),' from (select distinct rq from #tbltmp ) b
set @sql=@sql+'cast((select sum(num) from #tbltmp) as varchar)'set @sql=@sql+' union select ''TimeNum1'', '
select @sql=@sql+'cast((select top 1  TimeNum1 from temp1 where right(bzrq,2)='''+rq+''') as varchar),' from (select distinct rq from #tbltmp ) c
set @sql=@sql+'cast((select sum(TimeNum1) from (select distinct right(bzrq,2) rq,TimeNum1 from temp1) f) as varchar)'
--print @sql
set @sql='select * from  ('+@sql+') z  order by case z.[原因] when ''合计'' then ''300'' when ''TimeNum1'' then ''200'' when ''百分比'' then ''100'' else z.[原因] end desc  '
exec(@sql) 
drop table #tbltmp ,temp1/*
原因       01                             02                             03                             合計                             
-------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ 
休息       120                            60                             60                             240
換線       0                              15                             0                              15
換部品      20                             10                             0                              30
待排程      360                            180                            0                              540
待料       0                              15                             0                              15
5S       30                             0                              0                              30
合计       530                            280                            60                             870
TimeNum1 60                             60                             60                             180
百分比      36.81%                         19.44%                         4.17%                          */

Answer 4

最新版本
create table temp1
( 
bzrq nchar(8), 
bzzb nvarchar(8), 
bzline nvarchar(8), 
bzcplb nvarchar(8), 
bztype nvarchar(8), 
TimeNum int, 
TimeNum1 int) 
insert into temp1 
select '20080101','半成品A1','line1',' 組立前制','待排程',180,60 union all 
select '20080101','半成品A1','line1','組立前制','5S', 30,60 union all 
select '20080101','半成品A1','line1','組立前制','休息',60,60 union all 
select '20080101','半成品A1','line1','組立前制','換部品',10,60 union all 
select '20080101','半成品A1','line1','組立前制','換部品',10,60 union all 
select '20080101','半成品A1','line1','組立前制','待排程',180,60 union all 
select '20080101','半成品A1','line1','組立前制','休息',60,60 union all 
select '20080102','半成品A1','line1','組立前制','待排程',180,60 union all 
select '20080102','半成品A1','line1','組立前制','待料',15,60 union all 
select '20080103','半成品A1','line1','組立前制','休息',60,60 union all 
select '20080102','半成品A1','line1','組立前制','換部品',10,60 union all 
select '20080102','半成品A1','line1','組立前制','休息',60,60 union all 
select '20080102','半成品A1','line1','組立前制','換線',15,60 create  table #tbltmp 
( 
  bztype nchar(8), 
  rq nchar(2), 
  num int 
) 
insert into #tbltmp 
select a.bztype,a.rq,sum(TimeNum) as num from 
( 
    select bztype,right(bzrq,2)as rq,TimeNum from temp1 a 
) as a group by a.bztype,a.rq 
declare @sql varchar(8000) 
set @sql = ' select isnull(bztype,''百分比'') [原因],' 
select @sql= @sql+'case when grouping(bztype)=0 then rtrim(sum(case rq when '''+rq+''' then num  else 0 end)) else ltrim(str( sum(case rq when '''+rq+''' then num  else 0 end)*100.0/1440,8,2))+''%'' end as  ['+rq+'],' 
    from (select distinct rq from #tbltmp ) a 
set @sql = stuff(@sql,len(@sql),1,'') 
set @sql=@sql+ ',case when grouping(bztype)=0 then rtrim(sum(num)) else '''' end 合計 from #tbltmp group by bztype with rollup' set @sql=@sql+' union select ''合计'','
select @sql=@sql+'cast((select sum(num) from #tbltmp where rq='''+rq+''') as varchar),' from (select distinct rq from #tbltmp ) b
set @sql=@sql+'cast((select sum(num) from #tbltmp) as varchar)'set @sql=@sql+' union select ''TimeNum1'', '
select @sql=@sql+'cast((select top 1  TimeNum1 from temp1 where right(bzrq,2)='''+rq+''') as varchar),' from (select distinct rq from #tbltmp ) c
set @sql=@sql+'cast((select sum(TimeNum1) from (select distinct right(bzrq,2) rq,TimeNum1 from temp1) f) as varchar)'
--print @sql
set @sql='select * from  ('+@sql+') z  order by case z.[原因] when ''合计'' then ''300'' when ''TimeNum1'' then ''200'' when ''百分比'' then ''100'' else z.[原因] end desc  '
exec(@sql) 
drop table #tbltmp ,temp1/*
原因       01                             02                             03                             合計                             
-------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ 
休息       120                            60                             60                             240
換線       0                              15                             0                              15
換部品      20                             10                             0                              30
待排程      360                            180                            0                              540
待料       0                              15                             0                              15
5S       30                             0                              0                              30
合计       530                            280                            60                             870
TimeNum1 60                             60                             60                             180
百分比      36.81%                         19.44%                         4.17%                          */

Answer 5

混点分
create table temp1
( 
bzrq nchar(8), 
bzzb nvarchar(8), 
bzline nvarchar(8), 
bzcplb nvarchar(8), 
bztype nvarchar(8), 
TimeNum int, 
TimeNum1 int) 
insert into temp1 
select '20080101','半成品A1','line1',' 組立前制','待排程',180,60 union all 
select '20080101','半成品A1','line1','組立前制','5S', 30,60 union all 
select '20080101','半成品A1','line1','組立前制','休息',60,60 union all 
select '20080101','半成品A1','line1','組立前制','換部品',10,60 union all 
select '20080101','半成品A1','line1','組立前制','換部品',10,60 union all 
select '20080101','半成品A1','line1','組立前制','待排程',180,60 union all 
select '20080101','半成品A1','line1','組立前制','休息',60,60 union all 
select '20080102','半成品A1','line1','組立前制','待排程',180,60 union all 
select '20080102','半成品A1','line1','組立前制','待料',15,60 union all 
select '20080103','半成品A1','line1','組立前制','休息',60,60 union all 
select '20080102','半成品A1','line1','組立前制','換部品',10,60 union all 
select '20080102','半成品A1','line1','組立前制','休息',60,60 union all 
select '20080102','半成品A1','line1','組立前制','換線',15,60 create  table #tbltmp 
( 
  bztype nchar(8), 
  rq nchar(2), 
  num int 
) 
insert into #tbltmp 
select a.bztype,a.rq,sum(TimeNum) as num from 
( 
    select bztype,right(bzrq,2)as rq,TimeNum from temp1 a 
) as a group by a.bztype,a.rq 
declare @sql varchar(8000) 
set @sql = ' select isnull(bztype,''百分比'') [原因],' 
select @sql= @sql+'case when grouping(bztype)=0 then rtrim(sum(case rq when '''+rq+''' then num  else 0 end)) else ltrim(str( sum(case rq when '''+rq+''' then num  else 0 end)*100.0/1440,8,2))+''%'' end as  ['+rq+'],' 
    from (select distinct rq from #tbltmp ) a 
set @sql = stuff(@sql,len(@sql),1,'') 
set @sql=@sql+ ',case when grouping(bztype)=0 then rtrim(sum(num)) else '''' end 合計 from #tbltmp group by bztype with rollup' set @sql=@sql+' union select ''合计'','
select @sql=@sql+'cast((select sum(num) from #tbltmp where rq='''+rq+''') as varchar),' from (select distinct rq from #tbltmp ) b
set @sql=@sql+'cast((select sum(num) from #tbltmp) as varchar)'set @sql=@sql+' union select ''TimeNum1'', '
select @sql=@sql+'cast((select top 1  TimeNum1 from temp1 where right(bzrq,2)='''+rq+''') as varchar),' from (select distinct rq from #tbltmp ) c
set @sql=@sql+'cast((select sum(TimeNum1) from (select distinct right(bzrq,2) rq,TimeNum1 from temp1) f) as varchar)'
--print @sql
set @sql='select * from  ('+@sql+') z  order by case z.[原因] when ''合计'' then ''300'' when ''TimeNum1'' then ''200'' when ''百分比'' then ''100'' else z.[原因] end desc  '
exec(@sql) 
drop table #tbltmp ,temp1/*
原因       01                             02                             03                             合計                             
-------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ 
休息       120                            60                             60                             240
換線       0                              15                             0                              15
換部品      20                             10                             0                              30
待排程      360                            180                            0                              540
待料       0                              15                             0                              15
5S       30                             0                              0                              30
合计       530                            280                            60                             870
TimeNum1 60                             60                             60                             180
百分比      36.81%                         19.44%                         4.17%                          */

Answer 6

  传说有个方法叫 povid ？