求SQL语句

Question

表A
CI CITY
17501 琼海
17501 定安
24221 昌江
24221 白沙
24221 儋州
36311 保亭
36311 乐东
36311 五指山
36311 三亚表B
CI CITY1 CITY2 CITY3 CITY4
17501 琼海 定安
24221 昌江 白沙 儋州
36311 保亭 乐东 五指山 三亚在表A中，一个CI最多4条记录，怎么通过SQL语句将表A的数据转成表B的数据，谢谢！！！！

Answer 1

普通行列转换
问题：假设有张学生成绩表(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 2

---------------------------------
--  Author: htl258(Tony)
--  Date  : 2009-08-14 08:58:18
---------------------------------
--> 生成测试数据表:aIf not object_id('[a]') is null
Drop table [a]
Go
Create table [a]([CI] int,[CITY] nvarchar(3))
Insert a
Select 17501,'琼海' union all
Select 17501,'定安' union all
Select 24221,'昌江' union all
Select 24221,'白沙' union all
Select 24221,'儋州' union all
Select 36311,'保亭' union all
Select 36311,'乐东' union all
Select 36311,'五指山' union all
Select 36311,'三亚'
Go
--Select * from a-->SQL查询如下:select CI,
 CITY1=max(case rn when 1 then CITY ELSE '' END),
 CITY2=max(case rn when 2 then CITY ELSE '' END),
 CITY3=max(case rn when 3 then CITY ELSE '' END),
 CITY4=max(case rn when 4 then CITY ELSE '' END)
from (
    select rn=row_number()over(partition by ci order by ci),*
    from a
) t
group by CI
/*
CI          CITY1 CITY2 CITY3 CITY4
----------- ----- ----- ----- -----
17501       琼海    定安          
24221       昌江    白沙    儋州    
36311       保亭    乐东    五指山   三亚(3 行受影响)
*/

Answer 3

以上在SQL2005以上版本下运行,超过4行部份截掉.

Answer 4

DECLARE @TB TABLE([CI] VARCHAR(5), [CITY] NVARCHAR(3))
INSERT @TB 
SELECT '17501', N'琼海' UNION ALL 
SELECT '17501', N'定安' UNION ALL 
SELECT '24221', N'昌江' UNION ALL 
SELECT '24221', N'白沙' UNION ALL 
SELECT '24221', N'儋州' UNION ALL 
SELECT '36311', N'保亭' UNION ALL 
SELECT '36311', N'乐东' UNION ALL 
SELECT '36311', N'五指山' UNION ALL 
SELECT '36311', N'三亚'SELECT *,ID=IDENTITY(INT,1,1) INTO # FROM @TB ORDER BY CISELECT CI,
MAX(CASE WHEN SEQ=1 THEN CITY ELSE '' END) AS CITY1,
MAX(CASE WHEN SEQ=2 THEN CITY ELSE '' END) AS CITY2,
MAX(CASE WHEN SEQ=3 THEN CITY ELSE '' END) AS CITY3,
MAX(CASE WHEN SEQ=4 THEN CITY ELSE '' END) AS CITY4
FROM (SELECT *,SEQ=ID-(SELECT COUNT(*) FROM # WHERE CI<TA.CI) FROM # AS TA) TB
GROUP BY CIDROP TABLE #
/*
CI    CITY1 CITY2 CITY3 CITY4 
----- ----- ----- ----- ----- 
17501 琼海    定安          
24221 昌江    白沙    儋州    
36311 保亭    乐东    五指山   三亚
*/

Answer 5

declare @T table (ci int,city varchar(50))insert into @t 
select 17501, '琼海' union all
select 17501, '定安' union all
select 24221, '昌江' union all
select 24221, '白沙' union all
select 24221, '儋州' union all
select 36311, '保亭' union all 
select 36311, '乐东' union all
select 36311, '五指山' union all
select 36311, '三亚'  select * into #1 from 
( select *,id=row_number() over(partition by ci order by getdate()) from  @t) m
pivot
(
max(city) for id in([1],[2],[3],[4])
) pselect * from #1drop table #1/*
ci          1                                                  2                                                  3                                                  4
----------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
17501       琼海                                                 定安                                                 NULL                                               NULL
24221       昌江                                                 白沙                                                 儋州                                                 NULL
36311       保亭                                                 乐东                                                 五指山                                                三亚(3 行受影响)
*/