求一存储过程~~~~

Question

怎么如此转换成横放？BomM_ID     BomD2_D1LineID BomD1_UsePart                       BomD2_MateColor               
----------- -------------- --------------------------------    -----------------------------------------------------
1           1              大身                                       红色
1           1              大身                                       红色
1           1              大身                                       红色
1           1              大身                                       红色
1           1              大身                                       ww色
1           1              大身                                       红色
1           1              大身                                       红色
1           2              领                                        yellow
1           2              领                                        white
1           2              领                                        blue
1           2              领                                        black
1           2              领                                        ww色
1           2              领                                        粉色
1           2              领                                        红色（所影响的行数为 14 行）
BomM_ID     BomD2_D1LineID BomD1_UsePart                          
1            1             大身            红色     红色    红色   红色    ww色  红色  红色     
1            2             领              yellow  white  blue  black  ww色  粉色　红色

Answer 1

有完全相同的记录,sql 2000要用临时表.sql 2005可以用row_number

Answer 2

/*
普通行列转换
(爱新觉罗.毓华 2007-11-18于海南三亚)假设有张学生成绩表(tb)如下:
Name Subject Result
张三 语文　　74
张三 数学　　83
张三 物理　　93
李四 语文　　74
李四 数学　　84
李四 物理　　94
*/-------------------------------------------------------------------------
/*
想变成 
姓名         语文        数学        物理          
---------- ----------- ----------- ----------- 
李四         74          84          94
张三         74          83          93
*/create table tb
(
   Name    varchar(10) ,
   Subject varchar(10) ,
   Result  int
)insert into tb(Name , Subject , Result) values('张三' , '语文' , 74)
insert into tb(Name , Subject , Result) values('张三' , '数学' , 83)
insert into tb(Name , Subject , Result) values('张三' , '物理' , 93)
insert into tb(Name , Subject , Result) values('李四' , '语文' , 74)
insert into tb(Name , Subject , Result) values('李四' , '数学' , 84)
insert into tb(Name , Subject , Result) values('李四' , '物理' , 94)
go--静态SQL,指subject只有语文、数学、物理这三门课程。
select name 姓名,
  max(case subject when '语文' then result else 0 end) 语文,
  max(case subject when '数学' then result else 0 end) 数学,
  max(case subject when '物理' then result else 0 end) 物理
from tb
group by name
/*
姓名         语文        数学        物理          
---------- ----------- ----------- ----------- 
李四         74          84          94
张三         74          83          93
*/--动态SQL,指subject不止语文、数学、物理这三门课程。
declare @sql varchar(8000)
set @sql = 'select Name as ' + '姓名'
select @sql = @sql + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
from (select distinct Subject from tb) as a
set @sql = @sql + ' from tb group by name'
exec(@sql) 
/*
姓名         数学        物理        语文          
---------- ----------- ----------- ----------- 
李四         84          94          74
张三         83          93          74
*/-------------------------------------------------------------------
/*加个平均分，总分
姓名         语文        数学        物理        平均分                总分          
---------- ----------- ----------- ----------- -------------------- ----------- 
李四         74          84          94          84.00                252
张三         74          83          93          83.33                250
*/--静态SQL,指subject只有语文、数学、物理这三门课程。
select name 姓名,
  max(case subject when '语文' then result else 0 end) 语文,
  max(case subject when '数学' then result else 0 end) 数学,
  max(case subject when '物理' then result else 0 end) 物理,
  cast(avg(result*1.0) as decimal(18,2)) 平均分,
  sum(result) 总分
from tb
group by name
/*
姓名         语文        数学        物理        平均分                总分          
---------- ----------- ----------- ----------- -------------------- ----------- 
李四         74          84          94          84.00                252
张三         74          83          93          83.33                250
*/--动态SQL,指subject不止语文、数学、物理这三门课程。
declare @sql1 varchar(8000)
set @sql1 = 'select Name as ' + '姓名'
select @sql1 = @sql1 + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
from (select distinct Subject from tb) as a
set @sql1 = @sql1 + ' , cast(avg(result*1.0) as decimal(18,2)) 平均分,sum(result) 总分 from tb group by name'
exec(@sql1) 
/*
姓名         数学        物理        语文        平均分                总分          
---------- ----------- ----------- ----------- -------------------- ----------- 
李四         84          94          74          84.00                252
张三         83          93          74          83.33                250
*/drop table tb ---------------------------------------------------------
---------------------------------------------------------
/*
如果上述两表互相换一下：即姓名 语文 数学 物理
张三 74　　83　　93
李四 74　　84　　94想变成 
Name       Subject Result      
---------- ------- ----------- 
李四         语文      74
李四         数学      84
李四         物理      94
张三         语文      74
张三         数学      83
张三         物理      93
*/create table tb1
(
   姓名 varchar(10) ,
   语文 int ,
   数学 int ,
   物理 int
)insert into tb1(姓名 , 语文 , 数学 , 物理) values('张三',74,83,93)
insert into tb1(姓名 , 语文 , 数学 , 物理) values('李四',74,84,94)select * from
(
  select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1 
  union all
  select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1
  union all
  select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1
) t
order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '总分' then 4 end--------------------------------------------------------------------
/*加个平均分,总分
Name       Subject     Result               
---------- -------    -------------------- 
李四         语文      74.00
李四         数学      84.00
李四         物理      94.00
李四         平均分    84.00
李四         总分      252.00
张三         语文      74.00
张三         数学      83.00
张三         物理      93.00
张三         平均分    83.33
张三         总分      250.00
*/select * from
(
  select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1 
  union all
  select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1
  union all
  select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1
  union all
  select 姓名 as Name , Subject = '平均分' , Result = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb1
  union all
  select 姓名 as Name , Subject = '总分' , Result = 语文 + 数学 + 物理 from tb1
) t
order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 enddrop table tb1

Answer 3

create table tb(BomM_ID int, BomD2_D1LineID int, BomD1_UsePart varchar(10), BomD2_MateColor varchar(10))
insert into tb values(1, 1, '大身', '红色') 
insert into tb values(1, 1, '大身', '红色') 
insert into tb values(1, 1, '大身', '红色') 
insert into tb values(1, 1, '大身', '红色') 
insert into tb values(1, 1, '大身', 'ww色') 
insert into tb values(1, 1, '大身', '红色') 
insert into tb values(1, 1, '大身', '红色') 
insert into tb values(1, 2, '领' , 'yellow') 
insert into tb values(1, 2, '领' , 'white') 
insert into tb values(1, 2, '领' , 'blue') 
insert into tb values(1, 2, '领' , 'black') 
insert into tb values(1, 2, '领' , 'ww色') 
insert into tb values(1, 2, '领' , '粉色') 
insert into tb values(1, 2, '领' , '红色') 
goselect * , id = identity(int,1,1) into tmp from tb--静态SQL，指最多7个.
select BomM_ID , BomD2_D1LineID , BomD1_UsePart , 
  max(case px when 1 then BomD2_MateColor else '' end) 'BomD2_MateColor1',
  max(case px when 2 then BomD2_MateColor else '' end) 'BomD2_MateColor2',
  max(case px when 3 then BomD2_MateColor else '' end) 'BomD2_MateColor3',
  max(case px when 4 then BomD2_MateColor else '' end) 'BomD2_MateColor4',
  max(case px when 5 then BomD2_MateColor else '' end) 'BomD2_MateColor5',
  max(case px when 6 then BomD2_MateColor else '' end) 'BomD2_MateColor6',
  max(case px when 7 then BomD2_MateColor else '' end) 'BomD2_MateColor7'
from
(
  select * , px = (select count(1) from tmp where BomM_ID = t.BomM_ID and BomD2_D1LineID = t.BomD2_D1LineID and BomD1_UsePart = t.BomD1_UsePart and id < t.id) + 1 from tmp t
) t
group by BomM_ID , BomD2_D1LineID , BomD1_UsePart
/*
BomM_ID     BomD2_D1LineID BomD1_UsePart BomD2_MateColor1 BomD2_MateColor2 BomD2_MateColor3 BomD2_MateColor4 BomD2_MateColor5 BomD2_MateColor6 BomD2_MateColor7 
----------- -------------- ------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- 
1           1              大身            红色               红色               红色               红色               ww色              红色               红色
1           2              领             yellow           white            blue             black            ww色              粉色               红色（所影响的行数为 2 行）
*/drop table tb,tmp

Answer 4

类似下面：
呵呵，忘了，在@sql   中还要增加修改临时表的语句，即增加对应的列。 
create   table   tb   (bid   int,bname   varchar(10),mpid   int,mpname   varchar(10),con   int,bcon   int) 
insert   into   tb   select   10,'董事会',1,'FB01A',60,5 
insert   into   tb   select   10,'董事会',2,'FB01B',60,0 
insert   into   tb   select   10,'董事会',3,'FB01C',60,0 
insert   into   tb   select   10,'董事会',4,'FB02A',60,8 
insert   into   tb   select   10,'董事会',5,'FB02B',60,0 
insert   into   tb   select   10,'董事会',6,'FB02C',60,2 
insert   into   tb   select   19,'营业',1,'FB01A',60,0 
insert   into   tb   select   19,'营业',2,'FB01B',60,0 
insert   into   tb   select   19,'营业',3,'FB01C',60,0 
insert   into   tb   select   19,'营业',4,'FB02A',60,0 
insert   into   tb   select   19,'营业',5,'FB02B',60,0 
insert   into   tb   select   19,'营业',6,'FB02C',60,0 
declare   @sql   varchar(8000) 
create   table   #tt(mpid   int,mpname   varchar(1000),con   int) 
set   @sql   =   'alter   table   #tt   add     ' 
select     @sql=@sql+bname+'   int,   '   from   tb   group   by   bname 
set   @sql   =   left(@sql,len(@sql)-1) 
exec(@sql) 
set   @sql   =   'insert   #tt   select   mpid,mpname,con' 
select   @sql   =   @sql   +   '   ,   max(case   bname   when   '''   +   bname   +   '''   then   bcon   else   0   end)   ['   +   bname   +   '分配数]' 
from   (select   distinct   bname   from   tb   )   as   m 
set   @sql   =   @sql   +   '   from   tb   group   by   mpid,mpname,con' 
select   @sql 
exec(@sql) 
select   *   from   #tt 
drop   table   #tt 

Answer 5

create table tb(BomM_ID int, BomD2_D1LineID int, BomD1_UsePart varchar(10), BomD2_MateColor varchar(10))
insert into tb values(1, 1, '大身', '红色') 
insert into tb values(1, 1, '大身', '红色') 
insert into tb values(1, 1, '大身', '红色') 
insert into tb values(1, 1, '大身', '红色') 
insert into tb values(1, 1, '大身', 'ww色') 
insert into tb values(1, 1, '大身', '红色') 
insert into tb values(1, 1, '大身', '红色') 
insert into tb values(1, 2, '领' , 'yellow') 
insert into tb values(1, 2, '领' , 'white') 
insert into tb values(1, 2, '领' , 'blue') 
insert into tb values(1, 2, '领' , 'black') 
insert into tb values(1, 2, '领' , 'ww色') 
insert into tb values(1, 2, '领' , '粉色') 
insert into tb values(1, 2, '领' , '红色') 
goselect * , id = identity(int,1,1) into tmp from tb--静态SQL，指最多7个.
select BomM_ID , BomD2_D1LineID , BomD1_UsePart , 
  max(case px when 1 then BomD2_MateColor else '' end) 'BomD2_MateColor1',
  max(case px when 2 then BomD2_MateColor else '' end) 'BomD2_MateColor2',
  max(case px when 3 then BomD2_MateColor else '' end) 'BomD2_MateColor3',
  max(case px when 4 then BomD2_MateColor else '' end) 'BomD2_MateColor4',
  max(case px when 5 then BomD2_MateColor else '' end) 'BomD2_MateColor5',
  max(case px when 6 then BomD2_MateColor else '' end) 'BomD2_MateColor6',
  max(case px when 7 then BomD2_MateColor else '' end) 'BomD2_MateColor7'
from
(
  select * , px = (select count(1) from tmp where BomM_ID = t.BomM_ID and BomD2_D1LineID = t.BomD2_D1LineID and BomD1_UsePart = t.BomD1_UsePart and id < t.id) + 1 from tmp t
) t
group by BomM_ID , BomD2_D1LineID , BomD1_UsePart
/*
BomM_ID     BomD2_D1LineID BomD1_UsePart BomD2_MateColor1 BomD2_MateColor2 BomD2_MateColor3 BomD2_MateColor4 BomD2_MateColor5 BomD2_MateColor6 BomD2_MateColor7 
----------- -------------- ------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- 
1           1              大身            红色               红色               红色               红色               ww色              红色               红色
1           2              领             yellow           white            blue             black            ww色              粉色               红色（所影响的行数为 2 行）
*/--动态SQL，指个数不确定
declare @sql1 varchar(8000)
set @sql1 = 'select BomM_ID , BomD2_D1LineID , BomD1_UsePart'
select @sql1 = @sql1 + ' , max(case px when ''' + cast(px as varchar) + ''' then BomD2_MateColor else '' '' end) [' + cast(px as varchar) + ']'
from (select distinct px from (select * , px = (select count(1) from tmp where BomM_ID = t.BomM_ID and BomD2_D1LineID = t.BomD2_D1LineID and BomD1_UsePart = t.BomD1_UsePart and id < t.id) + 1 from tmp t) m) as a
set @sql1 = @sql1 + ' from (select * , px = (select count(1) from tmp where BomM_ID = t.BomM_ID and BomD2_D1LineID = t.BomD2_D1LineID and BomD1_UsePart = t.BomD1_UsePart and id < t.id) + 1 from tmp t) m group by BomM_ID , BomD2_D1LineID , BomD1_UsePart'
exec(@sql1) 
/*
BomM_ID     BomD2_D1LineID BomD1_UsePart 1          2          3          4          5          6          7          
----------- -------------- ------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 
1           1              大身            红色         红色         红色         红色         ww色        红色         红色
1           2              领             yellow     white      blue       black      ww色        粉色         红色
*/drop table tb,tmp

Answer 6

create table tb(BomM_ID int,BomD2_D1LineID int,  BomD1_UsePart   varchar(20),BomD2_MateColor varchar(20))
insert into tb
select 1,1, '大身','红色' union all 
select 1,1, '大身','红色' union all 
select 1,1, '大身','红色' union all 
select 1,1, '大身','红色' union all 
select 1,1, '大身','ww色' union all  
select 1,1, '大身','红色' union all 
select 1,1, '大身','红色' union all 
select 1,2, '领','yellow' union all 
select 1,2, '领','white'  union all 
select 1,2, '领','blue'   union all 
select 1,2, '领','black'  union all 
select 1,2, '领','ww色'   union all 
select 1,2, '领','粉色'   union all 
select 1,2, '领','红色'   
 
gocreate function f_getstr(@BomM_ID int,@BomD2_D1LineID int,@BomD1_UsePart varchar(20))
returns varchar(100)begin
declare @sql varchar(100)
set @sql=''
select @sql=@sql+','+BomD2_MateColor from tb 
where BomM_ID=@BomM_ID and BomD2_D1LineID=@BomD2_D1LineID and BomD1_UsePart=@BomD1_UsePart
set @sql=stuff(@sql,1,1,'')
return (@sql)
endgoselect BomM_ID,BomD2_D1LineId,BomD1_UsePart,dbo.f_getstr(BomM_ID,BomD2_D1LineId,BomD1_UsePart) as BomD2_MateColor  from tb
group by BomM_ID,BomD2_D1LineId,BomD1_UsePart
/*
BomM_ID BomD2_D1LineId   BomD1_UsePart                           BomD2_MateColor
-----------------------------------------------------------------------------------------------
1   1               大身                  红色,红色,红色,红色,ww色,红色,红色
1   2                领                  yellow,white,blue,black,ww色,粉色,红色
*/
drop table tb
drop function f_getstr

Answer 7

--如果是合并为一个字段,sql server 2000中要用函数.
create table tb(BomM_ID int, BomD2_D1LineID int, BomD1_UsePart varchar(10), BomD2_MateColor varchar(10))
insert into tb values(1, 1, '大身', '红色') 
insert into tb values(1, 1, '大身', '红色') 
insert into tb values(1, 1, '大身', '红色') 
insert into tb values(1, 1, '大身', '红色') 
insert into tb values(1, 1, '大身', 'ww色') 
insert into tb values(1, 1, '大身', '红色') 
insert into tb values(1, 1, '大身', '红色') 
insert into tb values(1, 2, '领' , 'yellow') 
insert into tb values(1, 2, '领' , 'white') 
insert into tb values(1, 2, '领' , 'blue') 
insert into tb values(1, 2, '领' , 'black') 
insert into tb values(1, 2, '领' , 'ww色') 
insert into tb values(1, 2, '领' , '粉色') 
insert into tb values(1, 2, '领' , '红色') 
go--创建一个合并的函数
create function f_hb(@BomM_ID int,@BomD2_D1LineID int , @BomD1_UsePart varchar(20))
returns varchar(8000)
as
begin
  declare @str varchar(8000)
  set @str = ''
  select @str = @str + ' ' + cast(BomD2_MateColor as varchar) from tb where BomM_ID = @BomM_ID and BomD2_D1LineID = @BomD2_D1LineID and BomD1_UsePart = @BomD1_UsePart
  set @str = right(@str , len(@str) - 1)
  return(@str)
End
go--调用自定义函数得到结果：
select distinct BomM_ID,BomD2_D1LineID,BomD1_UsePart ,dbo.f_hb(BomM_ID,BomD2_D1LineID,BomD1_UsePart) as BomD2_MateColor from tbdrop table tb
drop function f_hb/*
BomM_ID     BomD2_D1LineID BomD1_UsePart BomD2_MateColor                                                                                                                                                                                                                                                  
----------- -------------- ------------- --------------------------------------
1           1              大身            红色 红色 红色 红色 ww色 红色 红色
1           2              领             yellow white blue black ww色 粉色 红色（所影响的行数为 2 行）
*/

Answer 8

--sql server 2005不需要函数.
create table tb(BomM_ID int, BomD2_D1LineID int, BomD1_UsePart varchar(10), BomD2_MateColor varchar(10))
insert into tb values(1, 1, '大身', '红色') 
insert into tb values(1, 1, '大身', '红色') 
insert into tb values(1, 1, '大身', '红色') 
insert into tb values(1, 1, '大身', '红色') 
insert into tb values(1, 1, '大身', 'ww色') 
insert into tb values(1, 1, '大身', '红色') 
insert into tb values(1, 1, '大身', '红色') 
insert into tb values(1, 2, '领' , 'yellow') 
insert into tb values(1, 2, '领' , 'white') 
insert into tb values(1, 2, '领' , 'blue') 
insert into tb values(1, 2, '领' , 'black') 
insert into tb values(1, 2, '领' , 'ww色') 
insert into tb values(1, 2, '领' , '粉色') 
insert into tb values(1, 2, '领' , '红色') 
go
SELECT * FROM(SELECT DISTINCT BomM_ID,BomD2_D1LineID,BomD1_UsePart FROM tb)A OUTER APPLY(
        SELECT [BomD2_MateColor]= STUFF(REPLACE(REPLACE(
            (
                SELECT BomD2_MateColor FROM tb N
                WHERE BomM_ID = A.BomM_ID and BomD2_D1LineID = A.BomD2_D1LineID and BomD1_UsePart = A.BomD1_UsePart
                FOR XML AUTO
            ), '<N BomD2_MateColor="', ' '), '"/>', ' '), 1, 1, '')
)N
drop table tb
/*
BomM_ID     BomD2_D1LineID BomD1_UsePart BomD2_MateColor                                                                                                                                                                                                                                                  
----------- -------------- ------------- --------------------------------------
1           1              大身            红色 红色 红色 红色 ww色 红色 红色
1           2              领             yellow white blue black ww色 粉色 红色（所影响的行数为 2 行）
*/