SQL 行变列的疑难杂症.100分求解.

Question

/*
标题：普通行列转换(version 2.0)
作者：爱新觉罗.毓华 
时间：2008-03-09
地点：广东深圳
说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加sql server 2005的有关写法。问题：假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果)： 
姓名 语文 数学 物理 
---- ---- ---- ----
李四 74   84   94
张三 74   83   93
-------------------
*/create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
go--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)
select 姓名 as 姓名 ,
  max(case 课程 when '语文' then 分数 else 0 end) 语文,
  max(case 课程 when '数学' then 分数 else 0 end) 数学,
  max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql) --SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')---------------------------------/*
问题：在上述结果的基础上加平均分，总分，得到如下结果：
姓名 语文 数学 物理 平均分 总分 
---- ---- ---- ---- ------ ----
李四 74   84   94   84.00  252
张三 74   83   93   83.33  250
*/--SQL SERVER 2000 静态SQL。
select 姓名 姓名,
  max(case 课程 when '语文' then 分数 else 0 end) 语文,
  max(case 课程 when '数学' then 分数 else 0 end) 数学,
  max(case 课程 when '物理' then 分数 else 0 end) 物理,
  cast(avg(分数*1.0) as decimal(18,2)) 平均分,
  sum(分数) 总分
from tb
group by 姓名--SQL SERVER 2000 动态SQL。
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名'
exec(@sql) --SQL SERVER 2005 静态SQL。
select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b) m , 
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名')drop table tb    ------------------
------------------/*
问题：如果上述两表互相换一下：即表结构和数据为：
姓名 语文 数学 物理
张三 74　　83　　93
李四 74　　84　　94
想变成(得到如下结果)： 
姓名 课程 分数 
---- ---- ----
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
--------------
*/create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)
insert into tb values('张三',74,83,93)
insert into tb values('李四',74,84,94)
go--SQL SERVER 2000 静态SQL。
select * from
(
 select 姓名 , 课程 = '语文' , 分数 = 语文 from tb 
 union all
 select 姓名 , 课程 = '数学' , 分数 = 数学 from tb
 union all
 select 姓名 , 课程 = '物理' , 分数 = 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end--SQL SERVER 2000 动态SQL。
--调用系统表动态生态。
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'
from syscolumns 
where name! = N'姓名' and ID = object_id('tb') --表名tb，不包含列名为姓名的其它列
order by colid asc
exec(@sql + ' order by 姓名 ')--SQL SERVER 2005 动态SQL。
select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t--SQL SERVER 2005 动态SQL，同SQL SERVER 2000 动态SQL。--------------------
/*
问题：在上述的结果上加个平均分，总分，得到如下结果：
姓名 课程   分数
---- ------ ------
李四 语文   74.00
李四 数学   84.00
李四 物理   94.00
李四 平均分 84.00
李四 总分   252.00
张三 语文   74.00
张三 数学   83.00
张三 物理   93.00
张三 平均分 83.33
张三 总分   250.00
------------------
*/select * from
(
 select 姓名 as 姓名 , 课程 = '语文' , 分数 = 语文 from tb 
 union all
 select 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 from tb
 union all
 select 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 from tb
 union all
 select 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb
 union all
 select 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 + 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 enddrop table tb

Answer 1

declare @sql varchar(8000) 
set @sql='select '  
select  @sql=@sql+'max(case s_name when '''+ s_name +''' then s_id end) '+ s_name +' ,' 
from (select distinct s_name from step_1)  a order by s_order 
set @sql=left(@sql,len(@sql)-1) 
--print @sql 
set @sql=@sql+'  from step_1' 
exec(@sql) -------------这样好像只能是存储过程

Answer 2

declare @sql varchar(8000) 
set @sql='select '  
select  @sql=@sql+'max(case s_name when '''+ s_name +''' then s_id end) '+ s_name +' ,' 
from (select distinct s_name from step_1)  a  order by s_order  
set @sql=left(@sql,len(@sql)-1) 
--print @sql 
set @sql=@sql+'  from step_1' 
exec(@sql) 

Answer 3

declare @sql varchar(8000) 
set @sql='select '  
select  @sql=@sql+'max(case s_name when '''+ s_name +''' then s_id end) '+ s_name +' ,' 
from (select distinct s_name from step_1)  a  order by s_order  
set @sql=left(@sql,len(@sql)-1) 
--print @sql 
set @sql=@sql+'  from step_1' 
exec(@sql) 

Answer 4

declare @sql varchar(8000) 
set @sql='select '  
select  @sql=@sql+'max(case s_name when '''+ s_name +''' then s_id end) '+ s_name +' ,' 
from (select distinct top 100 percent s_name from step_1 order by s_order)  a 
set @sql=left(@sql,len(@sql)-1) 
set @sql=@sql+'  from step_1' 
exec(@sql) 

Answer 5

上当，上当
create table step_1 (s_name varchar(10),s_order int,s_id int)
goinsert step_1 select '步骤一',1 ,1
insert step_1 select '步骤二',2 ,2
insert step_1 select '步骤三',3 ,3
insert step_1 select '步骤四',4 ,4declare @sql varchar(8000) 
set @sql='select '  
select  @sql=@sql+'max(case s_name when '''+ s_name +''' then s_id end) '+ s_name +' ,' 
from (select top 100 percent s_name  from step_1 b where not exists(select 1 from step_1 where s_name = b.s_name and s_id < b.s_id)  order by s_order )  a 
set @sql=left(@sql,len(@sql)-1) 
--print @sql 
set @sql=@sql+'  from step_1' 
exec(@sql) 
drop table step_1

Answer 6

declare @sql varchar(8000) 
set @sql='select '  
select  @sql=@sql+'max(case s_name when '''+ s_name +''' then s_id end) '+ s_name +' ,' 
from (select distinct s_name from step_1)  a order by s_order 
set @sql=left(@sql,len(@sql)-1) 
--print @sql 
set @sql=@sql+'  from step_1' 
exec(@sql) ---------------创建视图..declare @sql varchar(8000) 
set @sql='select '  
select  @sql=@sql+'max(case s_name when '''+ s_name +''' then s_id end) '+ s_name +' ,' 
from (select distinct s_name from step_1)  a order by s_order 
set @sql=left(@sql,len(@sql)-1) 
--print @sql 
set @sql=@sql+'  from step_1' 
exec('create view v_test as '@sql) --这样试试.

Answer 7

背着灵魂漫步
用distinct 时order by的字段要出现在列表中，
所以(select distinct top 100 percent s_name from step_1 order by s_order) 这样不对了

Answer 8

create table step_1 (s_name varchar(10),s_order int,s_id int)
goinsert step_1 select '步骤一',1 ,1
insert step_1 select '步骤二',2 ,2
insert step_1 select '步骤三',3 ,3
insert step_1 select '步骤四',4 ,4declare @sql varchar(8000) 
set @sql='select '  
select  @sql=@sql+'max(case s_name when '''+ s_name +''' then s_id end) '+ s_name +' ,' 
from (select top 100 percent s_name  
      from step_1 b  
      where not exists(select 1 from step_1 where s_name = b.s_name and s_id < b.s_id)  
      order by s_order )  a 
set @sql=left(@sql,len(@sql)-1) 
--print @sql 
set @sql=@sql+'  from step_1' 
exec(@sql) 
drop table step_1/*
步骤一         步骤二         步骤三         步骤四         
----------- ----------- ----------- ----------- 
1           2           3           4警告: 聚合或其它 SET 操作消除了空值。
*/

Answer 9

select  @sql=@sql+'max(case s_name when '''+ s_name +''' then s_id end) '+ s_name +' ,' 
from (select distinct s_name from step_1)  a 这句后面多接一个ORDER BY 语句就OK了

Answer 10

CREATE TABLE step_1(s_id INT,s_name VARCHAR(10),sms_days INT,s_state INT,s_order INT)
GO
INSERT step_1 SELECT  1,      '步骤一'   ,   3,         0     ,   1 
UNION ALL SELECT  2 ,     '步骤二'  ,    3 ,        0    ,    2 
UNION ALL SELECT  3  ,    '步骤三' ,     3  ,       0   ,     3 
UNION ALL SELECT  4   ,   '步骤四',      5   ,      0  ,      4 
GO
CREATE PROC p
AS
BEGIN
DECLARE @s VARCHAR(8000)
SELECT @s=ISNULL(@s+',','SELECT ') + 'MAX(CASE s_name WHEN ''' + s_name + ''' THEN s_id END) ' + s_name
FROM step_1 GROUP BY s_name ORDER BY MAX(s_id)
EXEC(@s + ' FROM step_1')
END
GOCREATE VIEW v AS
SELECT * FROM OPENROWSET('MSDASQL',
   'DRIVER={SQL Server};SERVER=(local);UID=sa;PWD=***',
   'EXEC test.dbo.p') a
GOSELECT * FROM vGODROP VIEW v
DROP PROC p
DROP TABLE step_1GO

Answer 11

这个是可行的,但是要不distinct去掉,不然有错误,去掉也可以,s_name不会有重复的记录的如何将这个东西存放在视图中???

Answer 12

ORDER BY MAX(s_id)
请更正为
ORDER BY MAX(s_order)

Answer 13

create table step_1 (s_name varchar(10),s_order int,s_id int)
insert step_1 select '步骤一',1 ,1
insert step_1 select '步骤二',2 ,2
insert step_1 select '步骤三',3 ,3
insert step_1 select '步骤四',4 ,4
go
declare @sql varchar(8000) 
set @sql='select '  
select 
    @sql=isnull(@sql,'')+'max(case s_name when '''+ s_name +''' then s_id end) '+ s_name +' ,' 
from 
    (select 
         top 100 percent * 
     from 
         step_1 t 
     where 
         not exists(select 1 from step_1 where s_name=t.s_name and s_id<t.s_id) order by t.s_id) aset @sql=left(@sql,len(@sql)-1)+' from step_1' exec(@sql) 
godrop table step_1
go

Answer 14

declare @sql varchar(8000) 
set @sql='select '  
select  @sql=@sql+'max(case s_name when '''+ s_name +''' then s_id end) '+ s_name +' ,' 
from (select top 1000 s_name from step_1 order by s_order)  a  
set @sql=left(@sql,len(@sql)-1) 
--print @sql 
set @sql=@sql+'  from step_1' 
exec('create view v_test as '+@sql)这样就可以了,完全解决.
晚上回来给分

Answer 15

declare @sql varchar(8000) 
set @sql='select '  
select  @sql=@sql+'max(case s_name when '''+ s_name +''' then s_id end) '+ s_name +' ,' 
from (select distinct top 100 percent s_name,s_order from step_1 order by s_order)  a 
set @sql=left(@sql,len(@sql)-1) 
set @sql=@sql+'  from step_1' 
exec(@sql) 

Answer 16

create table step_1 (s_name varchar(10),s_order int,s_id int)
goinsert step_1 select '步骤一',1 ,1
insert step_1 select '步骤二',2 ,2
insert step_1 select '步骤三',3 ,3
insert step_1 select '步骤四',4 ,4declare @sql varchar(8000) 
set @sql='select '  
select  @sql=@sql+'max(case s_name when '''+ s_name +''' then s_id end) '+ s_name +' ,' 
from ( select top 100 percent s_name,s_order from step_1
order by s_order
)  a 
set @sql=left(@sql,len(@sql)-1) 
--print @sql 
set @sql=@sql+'  from step_1 ' 
exec(@sql) 步骤一         步骤二         步骤三         步骤四
----------- ----------- ----------- -----------
1           2           3           4

Answer 17

对了,现在我成功的将这个查询放入了一个视图里,
我想吧这个视图和其他的一个表(工程表)组合起来使用,
工程表(obj_3)示这样的:
工程编码         工程名称      工程类型   
------------ -------------- --------------
20242          铝厂             城区      
3525           测试二            城区     
35628225       测试三            城西  
这里的数据行数可能有几十行.
  刚才的视图(v_test)是这样的;
步骤一         步骤二         步骤三         步骤四         
----------- ----------- ----------- ----------- 
1           2           3           4
并且数据就一行.
我现在想组合成:
工程编码         工程名称      工程类型     步骤一      步骤二         步骤三         步骤四         
------------ -------------- --------------  ----------- ----------- ----------- -----------          
20242          铝厂             城区        1               2                   3           4        
3525           测试二            城区       1               2                   3           4        
35628225       测试三            城西       1               2                   3           4        

Answer 18

select * from obj_3 a,v_test b

Answer 19

再进价一步
若step_1中有如重复数据比如步骤一 1 1
步骤二 2 2
步骤三 3 3
步骤四 4 4
步骤四 3 4
步骤三 3 3
步骤二 3 2
步骤四 5 4
步骤一 5 1
步骤四 5 4可以使用如下动态语句来剔除多余数据declare @sql varchar(8000)
select distinct s_name,s_id into #step_1 from step_1   order by s_id
select @sql = isnull(@sql + ',' , '') + s_name from #step_1 order by s_id
print @sql
exec ('select * from (select s_id,s_name from step_1) a pivot (max(s_id) for s_name in (' + @sql + ')) b')
drop table #step_1

Answer 20

以上语句用的是临时表，似乎在select into语句中不能使用表变量