如何做这样的行列转换

Question

create table tbda_gh_hxcf    --钢号化学元素含量表
(
    f_ghbm      varchar(15)   Not Null,  --钢号编码 
    f_bzlb          int              Not Null default 2,     --标准类别(0国家1企业2内控)
    f_xh       int      Not Null,  --序号
    f_ysbm      varchar(15)     Not Null,         --元素编码
    f_bzfw          varchar(30)     Not Null default '',    --标准范围
    Constraint tbda_gh_hxcf_key Primary Key(f_ghbm,f_bzlb,f_xh)
)
数据如下
   1-1 1 1 a 78 
   1-1 1 2 b 7  
   1-1 1 3 c 3  
   1-1 2 1 a 67 
   1-1 2 2 b 23 
我要能够转换成
   f_ghbm  f_bzlb        a     b     c
   1-1     1             78    7     3
   1-1     2             67    23    null    
   

Answer 1

create  Table  t1--可用声明形式,这样不用drop table t1
(a Int,
 b Char(2),
 c Int)
Insert t1 Select  1,         'a1',             100
Union All Select  1,         'a2',             200
Union All Select  2,         'a1',             300
Union All Select  2,         'a2',             400
Union All Select  3,         'a3',             500
Union All Select  4,         'a4',             600Declare @S varchar(8000)
Set @S='select a'--可以扩展别名as
Select @S=@S+',SUM(Case b When '''+b+''' Then c Else 0 End) As '+b--case when形式也可, 
                                                                  --else 0可以写成else null,
                                                                  --sum可用max若在字符串等形式下,
                                                                  --也可以不需要else
                                                                  --['+b+']'
From t1 Group By b Order By b--from (select distinct b from t1) as a
set @s=@S+' From t1 Group By a'
exec(@s)
drop table t1--Result
/*
A       a1      a2      a3      a4 
1 100 200 0 0
2 300 400 0 0
3 0 0 500 0
4 0 0 0 600
*/
-----------------------------------------------------------------
create table t1
(a int,
 b int)
insert t1
select 1,2 union all
select 3,4 
select * from t1declare @sql varchar(8000)
set @sql=''
select @sql=@sql+''''+name+''' as [c],sum('+name+') countdata from t1 union all select ' from syscolumns where id=object_id('t1')
print @sql
set @sql=left(@sql,len(@sql)-16)
print @sql
exec('select '+@sql)drop table t1--Result
/*
a       b
1 2
3 4
--结果
c       countdata
a 4
b 6
*/
---------------------------------------------------------------
create table tb1 (id nvarchar(10),type nvarchar(10))
insert into tb1 select '11','a'--abc为放的位置tt 
      union all select '22','b' 
      union all select '33','c'
create table tb2 (n int,type nvarchar(10),num int)
insert into tb2 select '1','11','4' 
      union all select '1','11','5' 
      union all select '2','22','8' 
      union all select '3','22','5'
--查询处理
DECLARE @SQL VARCHAR(8000)
SET @SQL='select n '
SELECT @SQL= @SQL+',sum(case when type='+ttt+' then num else 0 end)['+tt+']' from
(select distinct a.type as tt,isnull(b.type,'0') as ttt from tb2 b right join tb1 a on a.id=b.type) b
set @sql=@sql+' from tb2 group by n'
print @sql
exec(@sql)
--删除测试环境
Drop Table tb1,tb2--Result
/*
n       a       b       c
1 9 0 0
2 0 8 0
3 0 5 0
*/--------------------------------------
create table aaa(id1 varchar(4),id2 varchar(4),id3 varchar(4),m1 varchar(4),m2 varchar(4),m3 varchar(4))
insert into aaa select 'a','a','a','am1','am2','am3'
select * from aaa
declare @s varchar(8000)
set @s=''
select 
    @s=@s+' union all select id1,id2,id3,'''+name+''' as m,val='+name+' from aaa'
from syscolumns where id=object_id('aaa') and name not in('id1','id2','id3') order by colid
set @s=stuff(@s,1,10,'')
exec(@s)
Drop Table aaa
--Result
/*
id1     id2     id3     m1      m2      m3
a a a am1 am2 am3
id1     id2     id3     m       val
--结果
a a a m1 am1
a a a m2 am2
a a a m3 am3
*/create table tb1
(经办人 varchar(20),台次 int,比率 decimal(9,2),总金额 decimal(9,2))insert into tb1(经办人,台次,总金额,比率)
values('张三',20,20000,0.2)insert into tb1(经办人,台次,总金额,比率)
values('李四',80,80000,0.8)select * from tb1
select id=identity(int,1,1),* into #T from tb1
select * from #Tdeclare @s1 varchar(8000),@s2 varchar(8000) ,@s3 varchar(8000),@s5 varchar(8000) ,@i varchar(10) 
select @s1='',@s2='',@s3='',@s5='',@i='0' 
select @s1=@s1+',@'+@i+' varchar(8000)' 
,@s2=@s2+',@'+@i+'=''id='''''+name+'''''''' 
,@s3=@s3+' select @'+@i+'=@'+@i+'+'',[''+cast(id as varchar)+'']=''''''+cast(['+name+'] as varchar)+'''''''' from #T' 
,@s5=@s5+'+'' union all select ''+@'+@i 
,@i=cast(@i as int)+1 
from Tempdb..syscolumns 
where object_id('Tempdb..#T')=id and name not in('id')
order by colid 
select @s1=substring(@s1,2,8000) 
,@s2=substring(@s2,2,8000) 
,@s5=substring(@s5,14,8000) 
print @s1 
print @s2
print @s3 
print @s5
--print 'declare  '+@s1+' select  '+@s2+@s3+'  exec('''+@s5+')'
exec('declare '+@s1+' select '+@s2+@s3+' exec('''+@s5+')') drop table #T
drop table tb1------------create table test
(
ID int,
型号 varchar(20),
颜色 varchar(20),
上市时间 varchar(20),
参考价 int,
尺寸 varchar(20),
重量 int
)
insert test
select 1,    '诺基亚-1',   '红色',    '2004年1月',   '1500',    '118M',    143 union all
select 2,    '诺基亚-2',   '金色',    '2005年1月',   '1400',    '110M',    140 union all
select 3,    '诺基亚-3',   '黑色',    '2006年1月',   '2000',    '100M',    100
select * from testdeclare  @s1  varchar(8000),         @s2  varchar(8000),         @s3  varchar(8000),         @s5  varchar(8000),         @i  varchar(10)  
select  @s1='',@s2='',@s3='',@s5='',@i='0'  
select  @s1=@s1+',@'+@i+'  varchar(8000)'  
  ,@s2=@s2+',@'+@i+'=''ID='''''+name+''''''''  
  ,@s3=@s3+'  select  @'+@i+'=@'+@i+'+'',[''+cast(ID  as  varchar)+'']=''''''+cast(isnull(['+name+'],'''')  as  varchar)+''''''''  from  test'  
  ,@s5=@s5+'+''  union  all  select  ''+@'+@i  
  ,@i=cast(@i  as  int)+1  
from  syscolumns 
where  object_id('test')=id  and  name<>'ID'  
order  by  colid  
 
select  @s1=substring(@s1,2,8000)  
  ,@s2=substring(@s2,2,8000)  
  ,@s5=substring(@s5,15,8000)  print 'declare  '+@s1+' select  '+@s2+@s3+'  exec('''+@s5+')'exec('declare  '+@s1+' select  '+@s2+@s3+'  exec('''+@s5+')')  

Answer 2

if object_id('pubs..tbda_gh_hxcf') is not null
   drop table tbda_gh_hxcf
go
create table tbda_gh_hxcf    --钢号化学元素含量表
(
    f_ghbm      varchar(15)   Not Null,  --钢号编码 
    f_bzlb          int              Not Null default 2,     --标准类别(0国家1企业2内控)
    f_xh       int      Not Null,  --序号
    f_ysbm      varchar(15)     Not Null,         --元素编码
    f_bzfw          varchar(30)     Not Null default '',    --标准范围
    Constraint tbda_gh_hxcf_key Primary Key(f_ghbm,f_bzlb,f_xh)
)
insert into tbda_gh_hxcf(f_ghbm,f_bzlb,f_xh,f_ysbm,f_bzfw) values('1-1', 1, 1, 'a', '78' )
insert into tbda_gh_hxcf(f_ghbm,f_bzlb,f_xh,f_ysbm,f_bzfw) values('1-1', 1, 2, 'b', '7' )
insert into tbda_gh_hxcf(f_ghbm,f_bzlb,f_xh,f_ysbm,f_bzfw) values('1-1', 1, 3, 'c', '3' )
insert into tbda_gh_hxcf(f_ghbm,f_bzlb,f_xh,f_ysbm,f_bzfw) values('1-1', 2, 1, 'a', '67' )
insert into tbda_gh_hxcf(f_ghbm,f_bzlb,f_xh,f_ysbm,f_bzfw) values('1-1', 2, 2, 'b', '23' )declare @sql varchar(8000)
set @sql = 'select f_ghbm , f_bzlb'
select @sql = @sql + ' , max(case f_ysbm when ''' + f_ysbm + ''' then f_bzfw end) [' + f_ysbm + ']'
from (select distinct f_ysbm from tbda_gh_hxcf) as a
set @sql = @sql + ' from tbda_gh_hxcf group by f_ghbm , f_bzlb'
exec(@sql) --结果
f_ghbm  f_bzlb  a  b  c   
------- ------- -- -- ----
1-1     1       78 7  3
1-1     2       67 23 NULL

Answer 3

还有两个问题,
1我要列头显示元素表的元素名称而不是编号,如何办到
create table tbda_ys     --元素档案
(
    f_ysbm    varchar(15)      Not Null,       --元素编码
    f_ysmc        varchar(15)      Not Null,               --元素名称
    Constraint tbda_ys_key Primary Key(f_ysbm)
)
2存储过程调用后我想把数据给其他程序使用,如何方法

Answer 4

create table tbda_gh_hxcf    --钢号化学元素含量表
(
    f_ghbm      varchar(15)   Not Null,  --钢号编码 
    f_bzlb          int              Not Null default 2,     --标准类别(0国家1企业2内控)
    f_xh       int      Not Null,  --序号
    f_ysbm      varchar(15)     Not Null,         --元素编码
    f_bzfw          varchar(30)     Not Null default '',    --标准范围
    Constraint tbda_gh_hxcf_key Primary Key(f_ghbm,f_bzlb,f_xh)
)insert tbda_gh_hxcf 
select '1-1', 1, 1, 'a', 78 
union all select   '1-1', 1, 2, 'b', 7  
union all select   '1-1', 1, 3, 'c', 3  
union all select   '1-1', 2, 1, 'a', 67 
union all select   '1-1', 2, 2, 'b', 23 
declare @sql nvarchar(4000)
set @sql='select f_ghbm, f_bzlb, '
select @sql=@sql+
   quotename(f_ysbm)+'=max( case when f_ysbm='''+f_ysbm+''' then f_bzfw end), '
from tbda_gh_hxcf 
group by f_ysbm
select @sql=left(@sql, len(@sql)-1), @sql=@sql+' from tbda_gh_hxcf group by f_ghbm, f_bzlb'
exec(@sql)drop table tbda_gh_hxcf

Answer 5

if  object_id('tbda_gh_hxcf_plfw') is not null drop table tbda_gh_hxcf_plfw
    Declare @S varchar(8000)
    Set @S='select f_ghbm,f_bzlb,'
    Select @S=@S+',max(Case f_ysbm When '''+f_ysbm+''' Then f_plfw Else Null End) As ['+a.f_ysmc+']'
    From (select f_ysbm,f_ywmc from tbda_ys) as a Order By f_ysbm 
    set @s=@S+' into tbda_gh_hxcf_plfw From tbda_gh_hxcf Group By f_ghbm,f_bzlb'
    exec(@s)

Answer 6

if  object_id('tbda_gh_hxcf_plfw') is not null drop table tbda_gh_hxcf_plfw
    Declare @S varchar(8000)
    Set @S='select f_ghbm,f_bzlb '
    Select @S=@S+',max(Case f_ysbm When '''+f_ysbm+''' Then f_bzfw Else Null End) As ['+a.f_ysmc+']'
    From (select f_ysbm,f_ysmc from tbda_ys ) as a Order By f_ysbm 
    set @s=@S+' into tbda_gh_hxcf_plfw From tbda_gh_hxcf Group By f_ghbm,f_bzlb'
    exec(@s)

Answer 7

用上的分解一下静态的方法
create table tbda_gh_hxcf    --钢号化学元素含量表
(
    f_ghbm      varchar(15)   Not Null,  --钢号编码 
    f_bzlb          int              Not Null default 2,     --标准类别(0国家1企业2内控)
    f_xh       int      Not Null,  --序号
    f_ysbm      varchar(15)     Not Null,         --元素编码
    f_bzfw          varchar(30)     Not Null default '',    --标准范围
    Constraint tbda_gh_hxcf_key Primary Key(f_ghbm,f_bzlb,f_xh)
)
insert into tbda_gh_hxcf(f_ghbm,f_bzlb,f_xh,f_ysbm,f_bzfw) values('1-1', 1, 1, 'a', '78' )
insert into tbda_gh_hxcf(f_ghbm,f_bzlb,f_xh,f_ysbm,f_bzfw) values('1-1', 1, 2, 'b', '7' )
insert into tbda_gh_hxcf(f_ghbm,f_bzlb,f_xh,f_ysbm,f_bzfw) values('1-1', 1, 3, 'c', '3' )
insert into tbda_gh_hxcf(f_ghbm,f_bzlb,f_xh,f_ysbm,f_bzfw) values('1-1', 2, 1, 'a', '67' )
insert into tbda_gh_hxcf(f_ghbm,f_bzlb,f_xh,f_ysbm,f_bzfw) values('1-1', 2, 2, 'b', '23' )select f_ghbm , f_bzlb , max(case f_ysbm when 'a' then f_bzfw end) [a] , 
max(case f_ysbm when 'b' then f_bzfw end) [b] , 
isnull(max(case f_ysbm when 'c' then f_bzfw end),'') [c] 
from tbda_gh_hxcf group by f_ghbm , f_bzlb
f_ghbm          f_bzlb      a                              b                              c                              
--------------- ----------- ------------------------------ ------------------------------ ------------------------------ 
1-1             1           78                             7                              3
1-1             2           67                             23                             （所影响的行数为 2 行）警告: 聚合或其它 SET 操作消除了空值。

Answer 8

还有两个问题,
1我要列头显示元素表的元素名称而不是编号,如何办到
create table tbda_ys     --元素档案
(
    f_ysbm    varchar(15)      Not Null,       --元素编码
    f_ysmc        varchar(15)      Not Null,               --元素名称
    Constraint tbda_ys_key Primary Key(f_ysbm)
)
2存储过程调用后我想把数据给其他程序使用,如何方法
---------------------------------------------------------------------
--我自己加的三个颜色.
if object_id('pubs..tbda_gh_hxcf') is not null
   drop table tbda_gh_hxcf
go
create table tbda_gh_hxcf    --钢号化学元素含量表
(
    f_ghbm      varchar(15)   Not Null,  --钢号编码 
    f_bzlb          int              Not Null default 2,     --标准类别(0国家1企业2内控)
    f_xh       int      Not Null,  --序号
    f_ysbm      varchar(15)     Not Null,         --元素编码
    f_bzfw          varchar(30)     Not Null default '',    --标准范围
    Constraint tbda_gh_hxcf_key Primary Key(f_ghbm,f_bzlb,f_xh)
)
insert into tbda_gh_hxcf(f_ghbm,f_bzlb,f_xh,f_ysbm,f_bzfw) values('1-1', 1, 1, 'a', '78' )
insert into tbda_gh_hxcf(f_ghbm,f_bzlb,f_xh,f_ysbm,f_bzfw) values('1-1', 1, 2, 'b', '7' )
insert into tbda_gh_hxcf(f_ghbm,f_bzlb,f_xh,f_ysbm,f_bzfw) values('1-1', 1, 3, 'c', '3' )
insert into tbda_gh_hxcf(f_ghbm,f_bzlb,f_xh,f_ysbm,f_bzfw) values('1-1', 2, 1, 'a', '67' )
insert into tbda_gh_hxcf(f_ghbm,f_bzlb,f_xh,f_ysbm,f_bzfw) values('1-1', 2, 2, 'b', '23' )
goif object_id('pubs..tbda_ys') is not null
   drop table tbda_ys
go
create table tbda_ys     --元素档案
(
    f_ysbm    varchar(15)      Not Null,       --元素编码
    f_ysmc        varchar(15)      Not Null,               --元素名称
    Constraint tbda_ys_key Primary Key(f_ysbm)
)
insert into tbda_ys(f_ysbm,f_ysmc) values('a','红色')
insert into tbda_ys(f_ysbm,f_ysmc) values('b','黄色')
insert into tbda_ys(f_ysbm,f_ysmc) values('c','蓝色')
goif object_id('pubs..test') is not null
   drop table test
go
select a.f_ghbm , a.f_bzlb , b.f_ysmc , a.f_bzfw into test from tbda_gh_hxcf a , tbda_ys b where a.f_ysbm = b.f_ysbm
declare @sql varchar(8000)
set @sql = 'select f_ghbm , f_bzlb'
select @sql = @sql + ' , max(case f_ysmc when ''' + f_ysmc + ''' then f_bzfw end) [' + f_ysmc + ']'
from (select distinct f_ysmc from test) as a
set @sql = @sql + ' from test group by f_ghbm , f_bzlb'
exec(@sql) drop table tbda_gh_hxcf
drop table tbda_ys
drop table test--结果
f_ghbm          f_bzlb      红色  黄色  蓝色
--------------- ----------- ----  ----  ----
1-1             1           78    7     3
1-1             2           67    23    NULL