关于多行合并成一行的SQL查询语句

 在一个表中有多个单号，

    dh          xh        xm       zjhm                  flb
002001090001	1	李四	12334              	3
002001090001	2	保证人2	身份证2	                3
002001090001	5	保证人5	身份证5	                3
002001090001	6	保证人6	身份证6	                3
002001090001	9	保证人9	身份证9	                3
002001090002	1	aaa	身份qq	                3
002001090002	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

上面的情况。大家帮忙看看。怎么实现！谢谢！

12 个解决方案

#1

行列转换，精化里面有一卡车，自己翻吧

#2

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', 3

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
/*
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  NULL
002001090002 aaa  bbb  NULL NULL NULL NULL NULL NULL NULL NULL 身份qq  qqqq  NULL  NULL  NULL  NULL  NULL  NULL  NULL  NULL

*/

#3

引用 1 楼 jinjazz 的回复:

行列转换，精化里面有一卡车，自己翻吧

呵呵，估计不止一卡车。。。。呵呵

#4

行列看了挺多 就是不太会写~

#5

--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    

#6

学习

#7

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',3
select 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                 身份证9
002001090002         aaa                  bbb                  NULL                 NULL                 NULL                 身份qq                 qqqq                 NULL                 NULL                 NULL

(2 行受影响)

#8

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

#9

up

#10

if object_id('tb') is not null drop table tb
go
create 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', 3
go
declare @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 10
select @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 10
select @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  NULL
002001090002 aaa  bbb  NULL NULL NULL NULL NULL NULL NULL NULL 身份qq  qqqq  NULL  NULL  NULL  NULL  NULL  NULL  NULL  NULL

(2 行受影响)
*/

#11

该回复于2009-05-25 15:03:20被版主删除

#12

有关行列转换这个事，我也有个大的问题，目前能用SELECT * FROM #TEST1查出下面这个表，
ID 姓名 职位 部门 工号 调整前基本工资1
1 李小小 助理 人事部 GX800010 1200
2 李小小 助理 人事部 GX800010 1100
3 李小小 助理 人事部 GX800010 1000

需要的结果是：
姓名 职位 部门 工号 调整前基本工资3 调整前基本工资2 调整前基本工资1
李小小 助理 人事部 GX800010 1200 1100 1000

以上是调薪记录表，这里只是列举一个字段[调整前工资]，需要同样处理的有10来个字段，
数据有上千行，不同的人，记录数不一样，有的一条，有的二条，有的三条，每个人有个从1到3的ID号。
看各位大侠们有什么办法没有？
谢谢大家了。