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

最新推荐文章于 2024-12-05 10:58:06 发布

原创最新推荐文章于 2024-12-05 10:58:06 发布 · 置顶 · 2.3k 阅读

6 ·

CC 4.0 BY-SA版权

文章标签：

#sql #null #insert #sql server #pivot #table

本文介绍如何使用SQL进行行列转换，包括静态和动态写法，并通过具体示例展示了如何将表格数据从纵向转为横向，适用于SQL Server 2000及2005版本。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在一个表中有多个单号，

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

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

------------------------------------------------------------------------------------------------------------------------

SQL code

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

*/
--------------------------------------------------------------------------------------------------------------------------------------------SQL code --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

---------------------------------------------------------------------------------------------------------------------------------

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 行受影响)
*/