请教写一个SQL语句的写法，实现一种效果。

Question

源于一个想法，需要写一个SQL语句来实现，但没有成功。
即：把表2中的一行数据，作为表1新增的一列显示出来。（行列的数目是对应的）举例如下－－－－－－－－表1内容：
 ENFIELD    CHNAME
  A          序号1
  B          序号2
  C          序号3
  D          序号4
  E          序号5
 ...         ...
  
－－－－－－－－－－－－表2内容：
    A      B       C       D     E   ......  
   值1    值2     值3     值4    值5  ......－－－－－－－－－－－－要求SQL查询得到如下效果：把表2中的一行数据，作为表1新增的一列（VALUES）显示出来。 ENFIELD    CHNAME      VALUES
  A          序号1       值1
  B          序号2       值2
  C          序号3       值3
  D          序号4       值4
  E          序号5       值5
 ...         ...         ...－－－－－－－－－－－－－－－－－－－－－－在此向各位请教。 如果用SQL_Server能实现，那么用Oracle又如何写呢？多谢了！

Answer 1

先用union all将表2内容表成
a 值1
b 值2
c 值3
c 值4select 'a' col1,a col2 from tb2
union all
select 'b' col1,b col2 from tb2
...
...然后再join就行.

Answer 2

/*
标题：普通行列转换(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 课程
set @sql = '[' + @sql + ']'
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 3

--> liangCK小梁 于2008-07-26
--> 生成测试数据: #tb1
if object_id('tempdb.dbo.#tb1') is not null drop table #tb1
create table #tb1 (ENFIELD varchar(1),CHNAME nvarchar(5))
insert into #tb1
select 'A','序号1' union all
select 'B','序号2' union all
select 'C','序号3' union all
select 'D','序号4' union all
select 'E','序号5'
--> liangCK小梁 于2008-07-26
--> 生成测试数据: #tb2
if object_id('tempdb.dbo.#tb2') is not null drop table #tb2
create table #tb2 (A nvarchar(3),B nvarchar(3),C nvarchar(3),D nvarchar(3),E nvarchar(3))
insert into #tb2
select '值1','值2','值3','值4','值5'select a.*,b.col2
from #tb1 a
 left outer join
   (
      select 'a' col1,A col2 from #tb2
      union all
      select 'b' col1,B col2 from #tb2
      union all
      select 'c' col1,C col2 from #tb2
      union all
      select 'd' col1,D col2 from #tb2
      union all
      select 'e' col1,E col2 from #tb2
   ) b
  on a.ENFIELD=b.col1/*
ENFIELD CHNAME col2
------- ------ ----
A       序号1    值1
B       序号2    值2
C       序号3    值3
D       序号4    值4
E       序号5    值5(5 行受影响)
*/

Answer 4

也可以用 临时表 A      B      C      D    E  ......  
  值1    值2    值3    值4    值5  ...... declare @t table(ENFIELD char , col2 char)
insert @t
select A,'值1' from 表2 union all
select B,'值2' from 表2 union all
select C,'值3' from 表2 union all
select D,'值4' from 表2 union all
select E,'值4' from 表2 select 表1.ENFIELD,表1.CHNAME,@t.col2   from 表1 left jion @t on 表[email protected]

Answer 5

IF object_id('t1') IS NOT NULL
DROP TABLE t1
GO
IF object_id('t2') IS NOT NULL
DROP TABLE t2
GOCREATE TABLE t1(id VARCHAR(10),name VARCHAR(10))
GO
INSERT t1 SELECT 'a','serial1'
UNION ALL SELECT 'b','serial2'
UNION ALL SELECT 'c','serial3'
UNION ALL SELECT 'd','serial4'
GOCREATE TABLE t2(a VARCHAR(10),b VARCHAR(10),c VARCHAR(10),d VARCHAR(10))
GO
INSERT t2 SELECT 'v1','v2','v3','v4'
GO
DECLARE @sql VARCHAR(8000)
SELECT @sql=ISNULL(@sql + ' UNION ALL ','') + 'SELECT id=''' + name + ''',x=(SELECT TOP 1 ' + name + ' FROM t2)' FROM syscolumns WHERE id=object_id('t2')
EXEC('SELECT a.id ENFIELD ,a.name CHNAME,b.x [VALUES] FROM t1 a INNER JOIN (' + @sql + ') b ON a.id=b.id')/*
a serial1 v1
b serial2 v2
c serial3 v3
d serial4 v4
*/

Answer 6

declare @t1 table(ENFIELD char(1), CHNAME varchar(10))
insert into @t1 
 select 'A', '序号1' union all
 select 'B', '序号2' union all 
 select 'C', '序号3' union all 
 select 'D', '序号4' union all 
 select 'E', '序号5'declare @t2 table(A varchar(10), B  varchar(10), C varchar(10), D varchar(10), E  varchar(10))  
insert into @t2
select '值1', '值2', '值3', '值4', '值5' select t1.*, 
  (select case when t1.ENFIELD='A' then t2.A 
   when t1.ENFIELD='B' then t2.B 
               when t1.ENFIELD='C' then t2.C 
               when t1.ENFIELD='D' then t2.D 
               when t1.ENFIELD='E' then t2.E end
   from @t2 t2) [VALUES]
from @t1 t1
/*
ENFIELD CHNAME  VALUES
A 序号1 值1
B 序号2 值2
C 序号3 值3
D 序号4 值4
E 序号5 值5
*/

Answer 7

表2内容： 
    A      B      C      D    E  ......  
  值1    值2    值3    值4    值5  ...... 
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－其实我的表2有很多列啊。 我刚才数了一下，有73列。这73个，都要Union啊？？？？ 

Answer 8

那就先
用 
标题：普通行列转换(version 2.0)
作者：爱新觉罗.毓华 行列转换 然后在匹配

Answer 9

if object_id('t1') is not null
  drop table t1
go
create table t1(ENFIELD char(1), CHNAME varchar(10))
insert into t1 
 select 'A', '序号1' union all
 select 'B', '序号2' union all 
 select 'C', '序号3' union all 
 select 'D', '序号4' union all 
 select 'E', '序号5'if object_id('t2') is not null
  drop table t2
go
create table  t2(A varchar(10), B  varchar(10), C varchar(10), D varchar(10), E  varchar(10))  
insert into t2
select '值1', '值2', '值3', '值4', '值5' declare @s varchar(8000)
set @s='select *, (select case ENFIELD'
select @s=@s + ' when '''+ENFIELD+''' then '+ENFIELD from t1set @s=@s+' end from t2) [values] from t1'exec(@s)/*
ENFIELD CHNAME  VALUES
A       序号1    值1
B       序号2    值2
C       序号3    值3
D       序号4    值4
E       序号5    值5
*/

Answer 10

:(用5楼的方法，估计是组合的字符串太长了，超出了@sql所成承受的范围。
不能正确执行。
用10楼的方法，动态执行SQL，老是报错：
将 varchar 值 转换为数据类型为 int 的列时发生语法错误。
或
从字符串转换为 datetime 时发生语法错误。
但是实际上，我仔细查证，数据库字段内容都存储正确。－－－－这一点非常奇怪。
郁闷。总的来说，非常感谢楼上的各位！！

Answer 11

05下改用varchar(max)另外，如果在2000下，由于超长造成的话，那么可以分成多个字串变量执行exec(@sql1+@sql2+@sql3)

Answer 12

我用的是2000 发现用union all时候  如果字段类型不一致的时候，会报错：－－－“将 varchar 值 转换为数据类型为 int 的列时发生语法错误”

Answer 13

union all
需要前后进行联合的行集
1,列数一样
2,对应位置的列类型一致或可隐式转换。
比如
select 1 as id
union all
select 'x'
就会报错，第一行的select 1 as id确定的产生的结果集中 id为int
而第二个行集，产生的是varchar,它尝试将'x'转换为int所以报错select rtrim(1) as id
union all
select 'x'
这样就可以。

Answer 14

to 5楼 fcuandy ：   多谢您大力帮忙。   您的动态SQL、exec(@sql1+@sql2+@sql3)、select rtrim()。   三个问题搞完后，果然搞定！   非常感谢！
to 10楼 npkaida :   多谢！按照您提供的方法，再加上rtrim()函数，问题搞定！－－－－－－非常感谢楼上的各位提供的动态SQL方法，今天一天学习了不少东西。多谢各位了！

Answer 15

to fcuandy ： 您好！我已经正常查询出所需的结果了，如下：
-------
ENFIELD    CHNAME      VALUES 
  A          序号1        值1 
  B          序号2        值2 
  C          序号3        值3 
  D          序号4        值4 
  E          序号5        值5 
 ...         ...        ... 
在您写的SQL的基础上，我想做查询利用－－想加上复杂的查询条件， 这样的SQL能否做到？？如，我想做类似的查询，加上查询条件：
-----
... where (ENFIELD='A' and VALUES='值1') or (ENFIELD='C' and VALUES='值3')  
根据查询条件，得到这样的结果：
------
ENFIELD    CHNAME      VALUES 
  A          序号1        值1 
  C          序号3        值3-----我能否做到？？  该如何拼写SQL呢？ 谢谢。

Answer 16

if object_id('t1') is not null
  drop table t1
go
create table t1(ENFIELD char(1), CHNAME varchar(10))
insert into t1 
 select 'A', '序号1' union all
 select 'B', '序号2' union all 
 select 'C', '序号3' union all 
 select 'D', '序号4' union all 
 select 'E', '序号5'if object_id('t2') is not null
  drop table t2
go
create table  t2(A varchar(10), B  varchar(10), C varchar(10), D varchar(10), E  varchar(10))  
insert into t2
select '值1', '值2', '值3', '值4', '值5' declare @s varchar(8000)
set @s=';with t as (select *, (select case ENFIELD'
select @s=@s + ' when '''+ENFIELD+''' then '+ENFIELD from t1set @s=@s+' end from t2) [values] from t1)'+
       ' select * from t where (ENFIELD=''A'' and [VALUES]=''值1'') or (ENFIELD=''C'' and [VALUES]=''值3'')'exec(@s)

Answer 17

to npkaida ：运行提示信息：－－－    ';' 附近有语法错误。

Answer 18

22楼的写法是sql server 2005的 :)

Answer 19

你在他动态的基础上加上你的条件就可以了啥,
5楼那个动态SQL
适合2000

Answer 20

USE TEST
GOCREATE TABLE dbo.T1
(
ID INT IDENTITY(1,1) NOT NULL
,ENFIELD NVARCHAR(10) NULL
,CHNAME NVARCHAR(10) NULL
,CONSTRAINT PK_T1 PRIMARY KEY (ID ASC)
)
INSERT INTO dbo.T1
SELECT 'A','序号1' UNION ALL
SELECT 'B','序号2' UNION ALL
SELECT 'C','序号3' UNION ALL
SELECT 'D','序号4' UNION ALL
SELECT 'E','序号5' 
GO
create table dbo.T2
(
ID INT IDENTITY(1,1) NOT NULL
,A NVARCHAR(10) NULL
,B NVARCHAR(10) NULL
,C NVARCHAR(10) NULL
,D NVARCHAR(10) NULL
,E NVARCHAR(10) NULL
,CONSTRAINT PK_T2 PRIMARY KEY (ID ASC)
) 
INSERT INTO dbo.T2
SELECT '值1','值2', '值3', '值4', '值5'
GO
SELECT A.ENFIELD,A.CHNAME,B.VALU
FROM dbo.T1 AS A
INNER JOIN (
SELECT Turn,VALU
FROM
(
SELECT A,B,C,D,E FROM dbo.T2
) AS P
UNPIVOT
(
VALU FOR Turn IN (A,B,C,D,E)
)AS Temp) AS B
ON A.ENFIELD=B.Turn

Answer 21

to wangjianming45 :
 
   你这个是2005的吧？我的数据库是2000的。 :(

Answer 22

if object_id('表1')is not null
  drop table  表1
if object_id('表2') is not null
  drop table 表2 
if object_id('c') is not null
 drop table c
gocreate table 表1
( ENFIELD nvarchar(2),
  CHNAME  nvarchar(6)
)
insert into 表1 select 'A','序号1'
     union all  select 'B','序号2'
     union all  select 'C','序号3'
     union all  select 'D','序号4'
     union all  select 'E','序号5'
go
create table 表2
(  A nvarchar(4),
   B nvarchar(4),
   C nvarchar(4),
   D nvarchar(4),
   E nvarchar(4)
)
insert into 表2 select '值1' ,'值2',  '值3', '值4', '值5'
goselect A AS VALUSE,ENFIELD  INTO c FROM  
(select A ,'A' AS ENFIELD from 表2
union all
select B ,'B' from 表2
union all 
select C ,'C' from 表2
union all 
select D,'D' from 表2
union all 
select E ,'E'from 表2) bselect  a.ENFIELD,a.CHNAME,c.VALUSE FROM 表1 a inner join c  on a.ENFIELD=c.ENFIELD
------
A 序号1 值1
B 序号2 值2
C 序号3 值3
D 序号4 值4
E 序号5 值5

Answer 23

declare @s varchar(8000) 
set @s=';with t as (select *, (select case ENFIELD' 
select @s=@s + ' when '''+ENFIELD+''' then '+ENFIELD from t1 set @s=@s+' end from t2) [values] from t1)'+ 
      ' select * from t where (ENFIELD=''A'' and [VALUES]=''值1'') or (ENFIELD=''C'' and [VALUES]=''值3'')' exec(@s) －－－－－－－－－－－－－－－－－运行提示信息： ';' 附近有语法错误。请问，在SQL2000下，应该如何拼写动态SQL？

Answer 24

if object_id('t1') is not null
  drop table t1
go
create table t1(ENFIELD char(1), CHNAME varchar(10))
insert into t1 
 select 'A', '序号1' union all
 select 'B', '序号2' union all 
 select 'C', '序号3' union all 
 select 'D', '序号4' union all 
 select 'E', '序号5'if object_id('t2') is not null
  drop table t2
go
create table  t2(A varchar(10), B  varchar(10), C varchar(10), D varchar(10), E  varchar(10))  
insert into t2
select '值1', '值2', '值3', '值4', '值5' DECLARE @sql VARCHAR(8000)
SELECT @sql=ISNULL(@sql + ' UNION ALL ','') + 
       'SELECT ENFIELD=''' + name + ''',[VALUES]=(SELECT TOP 1 ' + name + ' FROM t2)' 
       FROM syscolumns 
       WHERE id=object_id('t2')
print @sql
EXEC('SELECT a.ENFIELD ,a.CHNAME,b.[VALUES] FROM t1 a'+
     ' INNER JOIN (' + @sql + ') b ON a.ENFIELD=b.ENFIELD'+
     ' where (a.ENFIELD=''A'' and b.[VALUES]=''值'') or (a.ENFIELD=''C'' and b.[VALUES]=''值'')')

Answer 25

多谢楼上各位了！为了同时适应SQLSERVER和Oracle的需求，最后还是选择了在前台寄主中循环拼写完成SQL。采用的是UNION ALL的方式。在寄主完成SQL的拼写，我所想到的，大致有两个好处吧：1.SQL字符串长度不受限制，能解决诸如SQLSERVQR中的varchar(8000)限制。
2.不使用存储过程或函数，这样就能同时适应于SQLSERVER和Oracle的需求，方便系统移植。缺点是可能会造成速度\效率降低一些。（我在JAVA中都append，实际运行效果还行吧。^_^)
我所拼写的SQL就是用的楼上各位提供的方法，可以通用于SQLSERVER和Oracle。最终拼写完成后的SQL大致的样子如下：Select ENFIELD,CHNAME,VALUE From t1 a INNER JOIN 
(
Select 'A' as ID,(select RTrim(A) from t2 ) as  VALUE FROM t2   --此处可以增加where条件
union all
Select 'B' as ID,(select RTrim(B) from t2 ) as  VALUE FROM t2
union all
Select 'C' as ID,(select RTrim(C) from t2 ) as  VALUE FROM t2
--如果有，继续拼接
......
) b 
on a.ENFIELD=b.ID 
and ((ENFIELD='A'and VALUE='值1') or (ENFIELD='C'and VALUE= '值3')) --过滤条件。
－－－－－－－－－再次表示感谢。尤其对npkaida 和 fcuandy 。 :)