求一SQL語句

Question

/*
普通行列转换
(爱新觉罗.毓华 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 1

declare @t table(a varchar(10),b varchar(10),c varchar(10))
insert into @t select '安全員','','張三'
insert into @t select '安全員','李四',''select a,max(b),max(c) from @t group by a--结果
--a                     
------------ ---------- ----------
--安全員        李四         張三

Answer 2

declare @t table(w_name varchar(10),myUnit varchar(10),myUnit2 varchar(10))
insert into @t select '安全員','','張三'
insert into @t select '安全員','李四',''
insert into @t select '安全','李四',''
insert into @t select '安全','','張三'select w_name,max(myUnit) as myUnit,max(myUnit2) as myUnit2 from @t group by w_name--结果
--w_name     myUnit     myUnit2
------------ ---------- ----------
--安全         李四         張三
--安全員        李四         張三

Answer 3

create table tb(w_name nvarchar(20),MyUnit nvarchar(20),myUnit2 nvarchar(20))
insert into tb values(N'安全員',null,N'張三')
insert into tb values(N'安全員',N'李四',null)select w_name
      ,max(myUnit) myUnit
      ,max(myUnit2) myUnit2
from tb
group by w_namedrop table tb/*
w_name               myUnit               myUnit2
-------------------- -------------------- --------------------
安全員                  李四                   張三
警告: 聚合或其他 SET 操作消除了空值。(1 row(s) affected)
*/

Answer 4

如果是如下表格w_name  myUnit     myUnit2 
安全員              張三一
安全員  李四一
安全員  李四二
安全員                張三二
想變成
w_name  myUnit     myUnit2 
安全員 李四一     張三一
安全員   李四二     張三二以上方法不行...

Answer 5

安全员都是一样？？declare @t table(w_name varchar(10),myUnit varchar(10),myUnit2 varchar(10))
insert @t select '安全员1','','张三一'
union all select '安全员1','李四一',''
union all select '安全员2','李四二',''
union all select '安全员2','','张三二'select w_name,
       myUnit=max(case w_name when w_name then myUnit end),
       myUnit2=max(case w_name when w_name then myUnit end)
from @t
group by w_name/*
w_name     myUnit     myUnit2    
---------- ---------- ---------- 
安全员1       李四一        李四一
安全员2       李四二        李四二
*/

Answer 6

汗，搞错了。declare @t table(w_name varchar(10),myUnit varchar(10),myUnit2 varchar(10))
insert @t select '安全员1','','张三一'
union all select '安全员1','李四一',''
union all select '安全员2','李四二',''
union all select '安全员2','','张三二'select w_name,
       myUnit=max(case w_name when w_name then myUnit end),
       myUnit2=max(case w_name when w_name then myUnit2 end)
from @t
group by w_name/*
w_name     myUnit     myUnit2    
---------- ---------- ---------- 
安全员1       李四一        张三一
安全员2       李四二        张三二
*/

Answer 7

declare @t table(w_name varchar(10),myUnit varchar(10),myUnit2 varchar(10))
insert @t select '安全员1','','张三一'
union all select '安全员1','李四一',''
union all select '安全员2','李四二',''
union all select '安全员2','','张三二'select w_name,
       myUnit=max(case w_name when w_name then myUnit end),
       myUnit2=max(case w_name when w_name then myUnit2 end)
from @t
group by w_name--w_name     myUnit     myUnit2
------------ ---------- ----------
--安全员1       李四一        张三一
--安全员2       李四二        张三二

Answer 8

declare @tb table(w_name nvarchar(20),MyUnit nvarchar(20),myUnit2 nvarchar(20))
insert into @tb values(N'安全員',null,N'張三一')
insert into @tb values(N'安全員',N'李四一',null)
insert into @tb values(N'安全員',N'李四二',null)
insert into @tb values(N'安全員',null,N'張三二')select *,IDENTITY(int,1,1) as id into # from @tb
select w_name
      ,max(myUnit) myUnit
      ,max(myUnit2) myUnit2
from (select *,(select count(*) from # where (myUnit is not null and t.myUnit is not null or myUnit2 is not null and t.myUnit2 is not null) and id<=t.id) as id1 from # t) as t1
group by w_name,id1drop table #
--结果
w_name               myUnit               myUnit2              
-------------------- -------------------- -------------------- 
安全員                  李四一                  張三一
安全員                  李四二                  張三二（所影响的行数为 2 行）警告: 聚合或其它 SET 操作消除了空值。

Answer 9

楼主可以结帖了！declare @t table(w_name varchar(10),myUnit varchar(10),myUnit2 varchar(10))
insert @t select '安全员1','','张三一'
union all select '安全员1','李四一',''
union all select '安全员2','李四二',''
union all select '安全员2','','张三二'
select w_name,max(myUnit) as myUnit,max(myUnit2) as myUnit2  from @t group by w_name/*
w_name         myUnit     myUnit2
------------ ---------- ----------
安全员1       李四一        张三一
安全员2       李四二        张三二
*/

Answer 10

==>Haiwer 方法虽然达到了效果，但有个问题，假如myUnit  myUnit2  字段不是固定的。而是由动态的SQL产生的？那如何解？

Answer 11

==>xinleile   不是安全员1及安全员2，全部都是安全员
==>To All  字段不是固定，是由动态SQL产生的，这里我只是为了举例

Answer 12

if object_id('tb') is not null
drop table tbcreate table tb(w_name varchar(10),myUnit varchar(10),myUnit2 varchar(10),myUnit3 varchar(10))
insert tb select '安全员','','张三',''
union all select '安全员','李四','',''
union all select '安全员','','','王五'
declare @exec varchar(200)
set @exec=''
select @exec=@exec+',max('+name+') as '''+name+''''  from syscolumns where id=object_id('tb') and  colid<>1 order by colid 
select @exec='select  w_name '+@exec+' from tb group by w_name'
exec (@exec)/*
w_name         myUnit     myUnit2   myUnit3
------------ ---------- ----------  --------
安全员          李四       张三      王五*/

Answer 13

这是动态的，试一下！if object_id('tb') is not null
drop table tbcreate table tb(w_name varchar(10),myUnit varchar(10),myUnit2 varchar(10),myUnit3 varchar(10))
insert tb select '安全员','','张三',''
union all select '安全员','李四','',''
union all select '安全员','','','王五'
declare @exec varchar(200)
set @exec=''
select @exec=@exec+',max('+name+') as '''+name+''''  from syscolumns where id=object_id('tb') and  colid<>1 order by colid 
select @exec='select  w_name '+@exec+' from tb group by w_name'
exec (@exec)/*
w_name         myUnit     myUnit2   myUnit3
------------ ---------- ----------  --------
安全员          李四       张三      王五*/
 

Answer 14

create table tb(w_name nvarchar(20),MyUnit nvarchar(20),myUnit2 nvarchar(20))
insert into tb values(N'安全員',null,N'張三一')
insert into tb values(N'安全員',N'李四一',null)
insert into tb values(N'安全員',N'李四二',null)
insert into tb values(N'安全員',null,N'張三二')declare @sql varchar(8000)
select @sql=isnull(@sql+' union all ','')+'select w_name,
max(case when right(myunit,1)='''+px+''' then myunit end) as MyUnit,
max(case when right(myUnit2,1)='''+px+''' then myUnit2 end) as myUnit2
from tb group by w_name'
from (select distinct right(myunit,1) as px from tb where right(myunit,1) is not null) tp
exec(@sql)w_name MyUnit myUnit2
安全員 李四二 張三二
安全員 李四一 張三一

Answer 15

有如下表declare @t table(w_name varchar(10),punit varchar(40),u_name varchar(10))
insert @t select '安全员','常平环城路','刘亮星'
union all select'安全员','西部一标','张锡辉'
union all select'安全员','西部四标','蒋国群'
union all select'安全员','威远大桥','蓝刚'
union all select'安全员','港口三标','彭孝波'
union all select'安全员','路面公司','蒋俊祥'
union all select'安全员','大岭山污水网','王峰'
union all select'安全员','港口大道','谢发万'
union all select'安全员','路面公司','何鸿章'
union all select'安全员','路面公司','饶辉'
union all select'安全员','路面公司','凌永青'select * from @t想查出如下效果:

Answer 16

declare   @t   table(w_name   varchar(10),punit   varchar(40),u_name   varchar(10)) 
insert   @t   select   '安全员','常平环城路','刘亮星' 
union   all   select'安全员','西部一标','张锡辉' 
union   all   select'安全员','西部四标','蒋国群' 
union   all   select'安全员','威远大桥','蓝刚' 
union   all   select'安全员','港口三标','彭孝波' 
union   all   select'安全员','路面公司','蒋俊祥' 
union   all   select'安全员','大岭山污水网','王峰' 
union   all   select'安全员','港口大道','谢发万' 
union   all   select'安全员','路面公司','何鸿章' 
union   all   select'安全员','路面公司','饶辉' 
union   all   select'安全员','路面公司','凌永青' select *,IDENTITY(int,1,1) as id into # from @t
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',max(case punit when '''+punit+''' then u_name end) as ['+punit+']'
from #
group by punit
order by min(id)
exec('select w_name'+@sql+' from (select *,(select count(*) from # where w_name=t.w_name and punit=t.punit and id<=t.id) as id1 from # t) as t1 group by w_name,id1')drop table #--结果
w_name     常平环城路      西部一标       西部四标       威远大桥       港口三标       路面公司       大岭山污水网     港口大道       
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 
安全员        刘亮星        张锡辉        蒋国群        蓝刚         彭孝波        蒋俊祥        王峰         谢发万
安全员        NULL       NULL       NULL       NULL       NULL       何鸿章        NULL       NULL
安全员        NULL       NULL       NULL       NULL       NULL       饶辉         NULL       NULL
安全员        NULL       NULL       NULL       NULL       NULL       凌永青        NULL       NULL警告: 聚合或其它 SET 操作消除了空值。

Answer 17

排序不同，看来你是按照字符排序的，这样可以省了临时表create  table testtb(w_name   varchar(10),punit   varchar(40),u_name   varchar(10)) 
insert   testtb   select   '安全员','常平环城路','刘亮星' 
union   all   select'安全员','西部一标','张锡辉' 
union   all   select'安全员','西部四标','蒋国群' 
union   all   select'安全员','威远大桥','蓝刚' 
union   all   select'安全员','港口三标','彭孝波' 
union   all   select'安全员','路面公司','蒋俊祥' 
union   all   select'安全员','大岭山污水网','王峰' 
union   all   select'安全员','港口大道','谢发万' 
union   all   select'安全员','路面公司','何鸿章' 
union   all   select'安全员','路面公司','饶辉' 
union   all   select'安全员','路面公司','凌永青' 
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',max(case punit when '''+punit+''' then u_name end) as ['+punit+']'
from testtb
group by punitexec('select w_name'+@sql+' from (select *,(select count(*) from testtb where w_name=t.w_name and punit=t.punit and u_name<=t.u_name) as id1 from testtb t) as t1 group by w_name,id1')
--结果
w_name     常平环城路      大岭山污水网     港口大道       港口三标       路面公司       威远大桥       西部四标       西部一标       
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 
安全员        刘亮星        王峰         谢发万        彭孝波        何鸿章        蓝刚         蒋国群        张锡辉
安全员        NULL       NULL       NULL       NULL       蒋俊祥        NULL       NULL       NULL
安全员        NULL       NULL       NULL       NULL       凌永青        NULL       NULL       NULL
安全员        NULL       NULL       NULL       NULL       饶辉         NULL       NULL       NULL警告: 聚合或其它 SET 操作消除了空值。

Answer 18

--可以这样
declare  @t table(w_name   varchar(10),punit   varchar(40),u_name   varchar(10)) 
insert  @t  select   '安全员','常平环城路','刘亮星' 
union   all   select'安全员','西部一标','张锡辉' 
union   all   select'安全员','西部四标','蒋国群' 
union   all   select'安全员','威远大桥','蓝刚' 
union   all   select'安全员','港口三标','彭孝波' 
union   all   select'安全员','路面公司','蒋俊祥' 
union   all   select'安全员','大岭山污水网','王峰' 
union   all   select'安全员','港口大道','谢发万' 
union   all   select'安全员','路面公司','何鸿章' 
union   all   select'安全员','路面公司','饶辉' 
union   all   select'安全员','路面公司','凌永青'SELECT w_name='安全员',* into #t FROM(SELECT DISTINCT punit FROM @t)A
OUTER APPLY(SELECT 
[u_name]= STUFF(REPLACE(REPLACE((SELECT u_name FROM @t N WHERE punit = A.punit FOR XML AUTO)
,'<N u_name="',','),'"/>', ''),1, 1, ''))Ndeclare @dtSTR varchar(8000)
select @dtSTR=isnull(@dtSTR,'') + ',[' + punit + ']=MAX(CASE WHEN punit=''' + punit + ''' THEN u_name ELSE '''' END)' from #t group by punit order by punit
select @dtSTR=stuff(@dtSTR,1,1,'')
--print @dtSTR
exec( 'select w_name,' +@dtStr + ' from #t group by w_name')
drop table #t--w_name 常平环城路                                                  威远大桥   西部四标 西部一标
--安全员 刘亮星 王峰 谢发万 彭孝波 蒋俊祥,何鸿章,饶辉,凌永青 蓝刚  蒋国群    张锡辉

Answer 19

declare  @t table(w_name   varchar(10),punit   varchar(40),u_name   varchar(10)) 
insert  @t  select   '安全员','常平环城路','刘亮星' 
union   all   select'安全员','西部一标','张锡辉' 
union   all   select'安全员','西部四标','蒋国群' 
union   all   select'安全员','威远大桥','蓝刚' 
union   all   select'安全员','港口三标','彭孝波' 
union   all   select'安全员','路面公司','蒋俊祥' 
union   all   select'安全员','大岭山污水网','王峰' 
union   all   select'安全员','港口大道','谢发万' 
union   all   select'安全员','路面公司','何鸿章' 
union   all   select'安全员','路面公司','饶辉' 
union   all   select'安全员','路面公司','凌永青'SELECT w_name='安全员',punit, 
STUFF((SELECT ',' + cast(u_name as varchar) AS [text()] --此处STUFF函数的目的是除去字符串首部的逗号 
FROM @t AS G2 
WHERE G2.punit = G1.punit
ORDER BY [u_name] 
FOR XML PATH('')), 1, 1, '') AS u_name into #t
FROM @t AS G1 
GROUP BY punit; declare @dtSTR varchar(8000)
select @dtSTR=isnull(@dtSTR,'') + ',[' + punit + ']=MAX(CASE WHEN punit=''' + punit + ''' THEN u_name ELSE '''' END)' from #t group by punit order by punit
select @dtSTR=stuff(@dtSTR,1,1,'')
--print @dtSTR
exec( 'select w_name,' +@dtStr + ' from #t group by w_name')
drop table #t

Answer 20

select w_name, max(myunit) myunit, max(myunit2) myunit2
from tb
group by w_name

Answer 21

if object_id('tb') is not null
drop table tbcreate  table tb(w_name   varchar(10),punit   varchar(40),u_name   varchar(10)) 
insert   tb   select   '安全员','常平环城路','刘亮星' 
union   all   select'安全员','西部一标','张锡辉' 
union   all   select'安全员','西部四标','蒋国群' 
union   all   select'安全员','威远大桥','蓝刚' 
union   all   select'安全员','港口三标','彭孝波' 
union   all   select'安全员','路面公司','蒋俊祥' 
union   all   select'安全员','大岭山污水网','王峰' 
union   all   select'安全员','港口大道','谢发万' 
union   all   select'安全员','路面公司','何鸿章' 
union   all   select'安全员','路面公司','饶辉' 
union   all   select'安全员','路面公司','凌永青' declare @exec varchar(2000),@i int
select  @exec='', @i=1
while @i<=(select count(distinct punit) as s from tb )
select  @exec=@exec+',['+punit+']=max(case when punit='''+punit+''' then u_name else '''' end )', @i=@i+1 from tb group by punit
set @exec='select w_name '+@exec+'from (select *,(select count(1) from tb where w_name=a.w_name and punit=a.punit and u_name<=a.u_name ) as cno from tb a  ) b
group by w_name,cno order by w_name'exec (@exec)--结果
/*
w_name     常平环城路      大岭山污水网     港口大道       港口三标       路面公司       威远大桥       西部四标       西部一标       
--------------------------------------------------------------------------------------------------------------------------
安全员        刘亮星        王峰              谢发万        彭孝波        何鸿章        蓝刚         蒋国群        张锡辉
安全员                                             蒋俊祥                      
安全员                                            凌永青                      
安全员                                             饶辉                       
*/