关于多行合并成一行的SQL查询语句
在一个表中有多个单号， dh xh xm zjhm flb002001090001 1 李四 12334 3002001090001 2 保证人2 身份证2 3002001090001 5 保证人5 身份证5 3002001090001 6 保证人6 身份证6 3002001090001 9 保证人9 身份证9 3002001090002 1 aaa 身份qq 3002001090002 2 bbb qqqq 3这个是表中数据，根据序号（XH），对XM 和zjhm 这两个进行合并成一条记录,分10个组想要的查询结果是 dh xm1 xm2 xm3 xm4 xm5 xm10 zjhm1 zjhm2 ........zjhm9 zjhm10 002001090001 李四 保证人2 null null 保证人5 null 12334 身份证2 身份证9 null 002001090002 aaa bbb null null null null aaa bbb null null
DECLARE @TB TABLE([dh] VARCHAR(12), [xh] INT, [xm] NVARCHAR(4), [zjhm] NVARCHAR(5), [flb] INT)INSERT @TB SELECT '002001090001', 1, N'李四', N'12334', 3 UNION ALL SELECT '002001090001', 2, N'保证人2', N'身份证2', 3 UNION ALL SELECT '002001090001', 5, N'保证人5', N'身份证5', 3 UNION ALL SELECT '002001090001', 6, N'保证人6', N'身份证6', 3 UNION ALL SELECT '002001090001', 9, N'保证人9', N'身份证9', 3 UNION ALL SELECT '002001090002', 1, N'aaa', N'身份qq', 3 UNION ALL SELECT '002001090002', 2, N'bbb', N'qqqq', 3SELECT dh, max(case when xh=1 then xm end) as xh1, max(case when xh=2 then xm end) as xh2, max(case when xh=3 then xm end) as xh3, max(case when xh=4 then xm end) as xh4, max(case when xh=5 then xm end) as xh5, max(case when xh=6 then xm end) as xh6, max(case when xh=7 then xm end) as xh7, max(case when xh=8 then xm end) as xh8, max(case when xh=9 then xm end) as xh9, max(case when xh=10 then xm end) as xh10, max(case when xh=1 then zjhm end) as zjhm1, max(case when xh=2 then zjhm end) as zjhm2, max(case when xh=3 then zjhm end) as zjhm3, max(case when xh=4 then zjhm end) as zjhm4, max(case when xh=5 then zjhm end) as zjhm5, max(case when xh=6 then zjhm end) as zjhm6, max(case when xh=7 then zjhm end) as zjhm7, max(case when xh=8 then zjhm end) as zjhm8, max(case when xh=9 then zjhm end) as zjhm9, max(case when xh=10 then zjhm end) as zjhm10FROM @TB GROUP BY dh/*dh xh1 xh2 xh3 xh4 xh5 xh6 xh7 xh8 xh9 xh10 zjhm1 zjhm2 zjhm3 zjhm4 zjhm5 zjhm6 zjhm7 zjhm8 zjhm9 zjhm10------------ ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----- ----- ----- ----- ----- ----- ----- ----- ----- ------002001090001 李四 保证人2 NULL NULL 保证人5 保证人6 NULL NULL 保证人9 NULL 12334 身份证2 NULL NULL 身份证5 身份证6 NULL NULL 身份证9 NULL002001090002 aaa bbb NULL NULL NULL NULL NULL NULL NULL NULL 身份qq qqqq NULL NULL NULL NULL NULL NULL NULL NULL*/
[解决办法]
--look this
/*
标题：普通行列转换(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
declare @tb table (dh nvarchar(20),xh int,xm nvarchar(20),zjhm varchar(20),flb int)insert into @tb select '002001090001',1,'李四','12334',3 union all select '002001090001',2,'保证人2','身份证2',3 union all select '002001090001',5,'保证人5','身份证5',3 union all select '002001090001',6,'保证人6','身份证6',3 union all select '002001090001',9,'保证人9','身份证9',3 union all select '002001090002',1,'aaa','身份qq',3 union all select '002001090002',2,'bbb','qqqq',3select a.*,b.zjhm1,zjhm2,zjhm5,zjhm6,zjhm9 from (select dh ,[1] as xh1, [2] as xh2,[5] as xh5,[6] as xh6,[9] as xh9 from (select dh,xh,XM from @tb) a pivot (max(xm) for xh in ([1],[2],[5],[6],[9])) b) a ,(select dh ,[1] as zjhm1, [2] as zjhm2,[5] as zjhm5,[6] as zjhm6,[9] as zjhm9 from (select dh,xh,zjhm from @tb) a pivot (max(zjhm) for xh in ([1],[2],[5],[6],[9])) b) b where a.dh=b.dh(7 行受影响)dh xh1 xh2 xh5 xh6 xh9 zjhm1 zjhm2 zjhm5 zjhm6 zjhm9-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------002001090001 李四 保证人2 保证人5 保证人6 保证人9 12334 身份证2 身份证5 身份证6 身份证9002001090002 aaa bbb NULL NULL NULL 身份qq qqqq NULL NULL NULL(2 行受影响)
[解决办法]
SELECT dh,
max(case when xh=1 then xm end) as xh1,
max(case when xh=2 then xm end) as xh2,
max(case when xh=3 then xm end) as xh3,
max(case when xh=4 then xm end) as xh4,
max(case when xh=5 then xm end) as xh5,
max(case when xh=6 then xm end) as xh6,
max(case when xh=7 then xm end) as xh7,
max(case when xh=8 then xm end) as xh8,
max(case when xh=9 then xm end) as xh9,
max(case when xh=10 then xm end) as xh10,
max(case when xh=1 then zjhm end) as zjhm1,
max(case when xh=2 then zjhm end) as zjhm2,
max(case when xh=3 then zjhm end) as zjhm3,
max(case when xh=4 then zjhm end) as zjhm4,
max(case when xh=5 then zjhm end) as zjhm5,
max(case when xh=6 then zjhm end) as zjhm6,
max(case when xh=7 then zjhm end) as zjhm7,
max(case when xh=8 then zjhm end) as zjhm8,
max(case when xh=9 then zjhm end) as zjhm9,
max(case when xh=10 then zjhm end) as zjhm10
FROM @TB
GROUP BY dh
[解决办法]
up
[解决办法]
if object_id('tb') is not null drop table tbgocreate table tb([dh] VARCHAR(12), [xh] INT, [xm] NVARCHAR(4), [zjhm] NVARCHAR(5), [flb] INT)INSERT TB SELECT '002001090001', 1, N'李四', N'12334', 3 UNION ALL SELECT '002001090001', 2, N'保证人2', N'身份证2', 3 UNION ALL SELECT '002001090001', 5, N'保证人5', N'身份证5', 3 UNION ALL SELECT '002001090001', 6, N'保证人6', N'身份证6', 3 UNION ALL SELECT '002001090001', 9, N'保证人9', N'身份证9', 3 UNION ALL SELECT '002001090002', 1, N'aaa', N'身份qq', 3 UNION ALL SELECT '002001090002', 2, N'bbb', N'qqqq', 3godeclare @s varchar(8000)select @s='select dh 'select @s=@s+',max(case xh when '+ltrim(number)+' then xm end) [xh'+ltrim(number)+']'from master..spt_values where type='p' and number between 1 and 10select @s=@s+',max(case xh when '+ltrim(number)+' then zjhm end) [zjhm'+ltrim(number)+']'from master..spt_values where type='p' and number between 1 and 10select @s=@s+' from tb group by dh'exec(@s)/*dh xh1 xh2 xh3 xh4 xh5 xh6 xh7 xh8 xh9 xh10 zjhm1 zjhm2 zjhm3 zjhm4 zjhm5 zjhm6 zjhm7 zjhm8 zjhm9 zjhm10------------ ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----- ----- ----- ----- ----- ----- ----- ----- ----- ------002001090001 李四 保证人2 NULL NULL 保证人5 保证人6 NULL NULL 保证人9 NULL 12334 身份证2 NULL NULL 身份证5 身份证6 NULL NULL 身份证9 NULL002001090002 aaa bbb NULL NULL NULL NULL NULL NULL NULL NULL 身份qq qqqq NULL NULL NULL NULL NULL NULL NULL NULL(2 行受影响)*/