行列转换例子

 

一、現有一個表TB1，其字段如下：(Name：代表姓名，Province：代表省份，Score：代表業務顧客數量)
Name  Province Score
李三  四川     5
小王  四川     3
小張  廣州     3
李三  廣州     2
小張  湖南     3
李三  湖南     4

得到下面的結果：
姓名  四川  廣州  湖南  總計
李三  5     2     4     11
小王  3     0     0     3
小張  0     3     3     6

注意：省份必須根據TB1表中所有出現的省份進行統計

 create table A
(
  Name varchar(10),
  Province varchar(20),
  Score int
)
insert A
select '李三','四川',5 union
select '小王','四川',3 union
select '小張','廣州',3 union
select '李三','廣州',2 union
select '小張','湖南',3 union
select '李三','湖南',4

--测试
declare @s varchar(8000)
set @s = ''

select @s = @s +','+Province+'= sum(case province when'''+province+'''then score else 0 end)'
from A group by Province order by Province

exec ('select name '+@s+',sum(Score) As Total from A group by Name order by Name')

--测试结果
--   Name  广州    湖南    四川     Total
-- 1 李三24511
-- 2 小王0033
-- 3 小張3306

--测试结束
drop table A

 

二、这是邹建写的一个交叉表的实例
--测试数据
CREATE TABLE #XS_REP_CLIENTPROD_CHX
(
 C_PTID varchar (16) NULL ,
 C_PTNAME varchar(80)  NULL,
 c_PCID varchar(50)  NULL,
 C_CUSTID varchar (15)  NULL ,
 c_CustName varchar (100) NULL ,
-- N_PRICE  numeric(18, 2) NULL ,
-- N_PRENOINVOICE numeric(18, 3) NULL ,
 N_SALEWEIGHT numeric(18, 3) NULL --,
-- N_INVOICEWEIGHT numeric(18, 3) NULL ,
-- N_NOINVOICE numeric(18, 3) NULL,
-- N_PRENOINVOICE_m  numeric(18, 3) NULL,
-- N_SALEWEIGHT_m numeric(18, 3) NULL ,
-- N_INVOICEWEIGHT_m numeric(18, 3) NULL ,
-- N_NOINVOICE_m numeric(18, 3) NULL
)

insert #XS_REP_CLIENTPROD_CHX
          select '001001','葡萄','0201','0201001','客户1',1
union all select '001001','葡萄','0201','0201002','客户2',2
union all select '001001','苹果','0201','0201001','客户1',3
union all select '001001','苹果','0201','0201002','客户2',4
go

--查询
declare @s varchar(8000)
set @s=''
select @s=@s+',['+C_PTNAME+']=sum(case C_PTNAME when '''
 +C_PTNAME+''' then N_SALEWEIGHT else 0 end)'
from #XS_REP_CLIENTPROD_CHX
group by C_PTNAME
exec('
select c_PCID=case
  when grouping(c_PCID)=1 then ''总计''
  else c_PCID end
 ,C_CUSTID=case
  when grouping(c_PCID)=1 then ''''
  when grouping(C_CUSTID)=1 then ''小计''
  else C_CUSTID end
 ,c_CustName=case
  when grouping(c_CustName)=0 then c_CustName
  else '''' end
 '+@s+'
from #XS_REP_CLIENTPROD_CHX
group by c_PCID,C_CUSTID,c_CustName with rollup
having (grouping(c_PCID)=1 or grouping(C_CUSTID)=1)
 or  grouping(c_CustName)=0
')
go

--删除测试
drop table #XS_REP_CLIENTPROD_CHX

/*--测试结果

c_PCID     C_CUSTID        c_CustName           苹果         葡萄    
---------- --------------- -------------------- ----------- ---------
0201       0201001         客户1                  3.000      1.000
0201       0201002         客户2                  4.000      2.000
0201       小计                                   7.000      3.000
总计                                              7.000      3.000

--*/

三 行列数

create table fbill(fauxqty int,Fdeptidname varchar(20),fsupplyidname varchar(20))
insert into fbill select 50,'信息部','十里庙  '
insert into fbill select 20,'业务部','经济开发'
insert into fbill select 20,'销售部','十里庙  '
insert into fbill select 1 ,'信息部','十里庙  '
insert into fbill select 2 ,'信息部','经济开发'
insert into fbill select 30,'业务部','经济开发'
insert into fbill select 5 ,'业务部','经济开发'
insert into fbill select 6 ,'业务部','经济开发'
go

declare @sql varchar(8000)
set @sql=''

select @sql=@sql+','+Fdeptidname+'=sum(case Fdeptidname when '''+Fdeptidname+''' then fauxqty else 0 end)'
from fbill group by Fdeptidname

set @sql='select  fsupplyidname as 客户'+@sql+' from fbill group by fsupplyidname'
exec(@sql)
go

/*
客户                   销售部         信息部         业务部        
-------------------- ----------- ----------- -----------
经济开发                 0           2           61
十里庙                  20          51          0
*/

drop table fbill
go