SQL查询案例：多行转换为一行（转）

最新推荐文章于 2024-08-27 11:39:18 发布
最新推荐文章于 2024-08-27 11:39:18 发布
阅读量470 收藏
点赞数
文章标签： java 数据库
原文链接：http://www.cnblogs.com/zhangdl/p/7851929.html
版权

SQL查询案例：多行转换为一行

使用通常的方式测试表与测试数据

CREATE TABLE TestTitle (

name   VARCHAR(10),

titleVARCHAR(10)

);

 

INSERT INTO TestTitle VALUES ('张三', '程序员');

INSERT INTO TestTitle VALUES ('张三', '系统管理员');

INSERT INTO TestTitle VALUES ('张三', '网络管理员');

INSERT INTO TestTitle VALUES ('李四', '项目经理');

INSERT INTO TestTitle VALUES ('李四', '系统分析员');

 

要求

对于测试数据，要求查询结果为：

张三程序员，系统管理员，网络管理员

李四项目经理，系统分析员

这种结构的结果。

思路

简单查看这个结果，很像对字符型的GROUP BY处理。

数值类型的可以SUM，但是字符类型的无法这么处理。

只好依次MAX(1) + MAX(2) + MAX(3)这种办法来处理。

实现

第一步，设置好分组的编号

SELECT

ROW_NUMBER() OVER(PARTITION BY name ORDER BY title) AS no,

name,

title

FROM

TestTitle

ORDER BY

name,

title

no                   name       title

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

                   1李四        系统分析员

                   2李四        项目经理

                   1张三        程序员

                   2张三        网络管理员

                   3张三        系统管理员

第二步，根据有编号的子查询，进行分组处理

SELECT

name,

CASE WHEN COUNT(title) = 1 THEN MAX(title)

       WHEN COUNT(title) = 2 THEN

         MAX( CASE WHEN SubQuery.no = 1 THEN title + '，' ELSE '' END )

         + MAX( CASE WHEN SubQuery.no = 2 THEN titleELSE '' END )

       WHEN COUNT(title) = 3 THEN

         MAX( CASE WHEN SubQuery.no = 1 THEN title + '，' ELSE '' END )

         + MAX( CASE WHEN SubQuery.no = 2 THEN title + '，'ELSE '' END )

         + MAX( CASE WHEN SubQuery.no = 3 THEN titleELSE '' END )

END AS new_title

FROM

(

SELECT

    ROW_NUMBER() OVER(PARTITION BY name ORDER BY title) AS no,

    name,

    title

FROM

    TestTitle

) subQuery

GROUP BY

name

执行结果

name       new_title

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

李四        系统分析员，项目经理

张三        程序员，网络管理员，系统管理员

 

 

 

对于SQL Server 2005 以上版本使用FOR XML的方式测试表与测试数据要求

与前面的一样

思路

首先把一个用户的数据,单独的读取出来

然后按照分组进行处理

实现

第一步 把一个用户的数据,单独的读取出来

SELECT

',' + title

FROM

TestTitle

WHERE

name = '张三'

FOR XML PATH('')

第二步Group By每个人

SELECT

name,

STUFF(

   (

   SELECT

     ',' + title

   FROM

     TestTitle subTitle

   WHERE

     name = TestTitle.name

   FOR XML PATH('')

   ),

   1, 1, '') AS allTitle

FROM

TestTitle

GROUP BY

name

执行结果

name      allTitle

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

李四        项目经理,系统分析员

张三        程序员,系统管理员,网络管理员

 

 

对于SQL Server 2005 以上版本使用 CTE 的处理方式 （使用递归方式处理）

 

WITH
t1  AS
(
  SELECT
    ROW_NUMBER() OVER(PARTITION BY name ORDER BY title) AS ID,
    name,
    title
  FROM
    TestTitle
),
t2 AS
(
  SELECT 
    t1.id, 
    t1.name, 
    CAST(t1.title AS varchar(100)) AS title
  FROM 
    t1 
  WHERE 
    t1.id = 1
  UNION ALL
  SELECT 
    t1.id, 
    t2.name, 
    CAST( t1.title + ',' + t2.title AS varchar(100)) AS title
  FROM 
    t1, t2
  WHERE 
    t1.name = t2.name
    AND t1.id = (t2.id + 1)
)
SELECT
  name, 
  title
FROM
  t2
WHERE
  NOT EXISTS (
    SELECT 1
    FROM t2 t22
    WHERE
      t2.name = t22.name
      AND t2.id < t22.id
  );

 

name       title

---------- -----------------------------------------------------------
-------------------------------
张三         系统管理员,网络管理员,程序员

李四         项目经理,系统分析员


(2 行受影响)

 

 

 

对于MySQL使用 GROUP_CONCAT 函数 的方式进行处理（非常简单）

 

mysql> SELECT
    ->   name,
    ->   GROUP_CONCAT(title) AS allTitle
    -> FROM
    ->   TestTitle
    -> GROUP BY
    ->   name;
+------+------------------------------+
| name | allTitle                     |
+------+------------------------------+
| 李四 | 项目经理,系统分析员          |
| 张三 | 程序员,系统管理员,网络管理员 |
+------+------------------------------+
2 rows in set (0.00 sec)

 

 

 

对于Oracle使用 WMSYS.WM_CONCAT 函数 的方式进行处理（也非常简单）

 

SQL> 
SQL> SELECT
  2    name,
  3    WMSYS.WM_CONCAT(title) AS allTitle
  4  FROM
  5    TestTitle
  6  GROUP BY
  7    name;

NAME
----------
ALLTITLE
-------------------------------------------
李四
项目经理,系统分析员

张三
程序员,系统管理员,网络管理员

 

 

 


 

对于 PostgreSQL 使用 string_agg  函数 的方式进行处理（也非常简单）

 

 

Test=#

Test=# SELECT

Test-#   name,

Test-#   string_agg(title,',') AS allTitle

Test-# FROM

Test-#   TestTitle

Test-# GROUP BY

Test-#   name;

 name |           alltitle

------+------------------------------

 李四 | 项目经理,系统分析员

 张三 | 程序员,系统管理员,网络管理员

(2 行记录)

 

 

 

 

对于 DB2 ，也是使用 CTE 递归的方式处理


WITH
t1 (id, name, title) AS
(
  SELECT
    ROW_NUMBER() OVER(PARTITION BY name ORDER BY title) AS ID,
    name,
    title
  FROM
    TestTitle
),
t2 (id, name, title) AS
(
  SELECT 
    t1.id, 
    t1.name, 
    CAST(t1.title AS varchar(100)) AS title
  FROM 
    t1 
  WHERE 
    t1.id = 1
  UNION ALL
  SELECT 
    t1.id, 
    t2.name, 
    CAST( t1.title || ',' || t2.title AS varchar(100)) AS title
  FROM 
    t1, t2
  WHERE 
    t1.name = t2.name
    AND t1.id = (t2.id + 1)
)
SELECT
  name, 
  title
FROM
  t2
WHERE
  NOT EXISTS (
    SELECT 1
    FROM t2 t22
    WHERE
      t2.name = t22.name
      AND t2.id < t22.id
  );

 

NAME       TITLE

---------- ---------------------------------------------------------------------
-------------------------------
SQL0347W  递归公共表表达式 "WZQ.T2" 可能包含无限循环。  SQLSTATE=01605

李四       项目经理,系统分析员

张三       网络管理员,系统管理员,程序员


  已选择 2 条记录，打印 1 条警告消息。

 

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

 

FOR XML PATH 的作用是把结果以xml文本的形式显示出来，也就是说，最终结果就是一个字符串，因此我们就不需要使用什么字符串合并函数了。

STUFF函数的原型是 Stuff(str1, start, len, str2)，作用是，删掉str1中start开始的len个字符，用str2替换。因此，可以起到在多个项之间插入分隔符。

比如，

 
select  ',' + name  from  student  for  xml path( '' )  group  by  class

输出结果可能是

,Jim,Kate,Tom,Sally

如果使用STUFF，可以删掉第一个空格

 
stuff( select  ',' + name  from  student  for  xml path( '' )  group  by  class, 1, 1,  '' )
如果使用的是hibernate 就要小心，用for xml path 查出来可能会是org.hibernate.lob.SerializableClob，而不是一个字符串，我现在是使用下面的方法转换的
[java]  view plain  copy
 
  1. if(value != null && value instanceof org.hibernate.lob.SerializableClob){  
  2.                         org.hibernate.lob.SerializableClob clob = (org.hibernate.lob.SerializableClob)value;  
  3.                         try {  
  4.                             Reader reader = clob.getCharacterStream();  
  5.                             BufferedReader br = new BufferedReader(reader);  
  6.                             StringBuilder sb = new StringBuilder();  
  7.                             String str = null;  
  8.                             while((str = br.readLine()) != null){  
  9.                                 sb.append(str);  
  10.                             }  
  11.                             _map.put(key.toUpperCase(), sb.toString());  
  12.                         } catch (SQLException e) {  
  13.                             e.printStackTrace();  
  14.                         } catch (IOException e) {  
  15.                             e.printStackTrace();  
  16.                         }  
  17.                     }  
不知道有没有更简单的方法进行配置

转载于:https://www.cnblogs.com/zhangdl/p/7851929.html

weixin_30376163
关注
SQL进阶技巧：如何不使用union all进行行转列？【三种方法实现】
石榴姐yyds
08-15 559
本文通过具体的案例来分析hivesql中具体行转列的技巧和方法，文中具体给出了三种方法，分别利用explode()函数、stack()函数以及inline()函数来完成，解决了传统union all进行行转列代码片段重复的问题，实现方式上较为优雅。
SQL进阶技巧：如何利用Stack()函数进行转列及动态行转列技巧？
石榴姐yyds
08-13 374
本文给出了一种利用stack()函数列转行的方法，优化了传统union all形式进行列转行的方式，同时针对stack()函数需要枚举的缺陷，给出了一种动态列转行的方法。
SQL多行变一行的方法
11-09
SQL方法，将一列中的多行变为一行，将按一定条件筛查出来的多行数据变为一行数据
把多条记录合并成一条的SQL语句
dawn_xiao的专栏
04-05 1万+
SQL code问题描述： 无论是在sql 2000，还是在 sql 2005 中,都没有提供字符串的聚合函数， 所以，当我们在处理下列要求时，会比较麻烦： 有表tb, 如下： id value ----- ------ 1 aa 1 bb 2 aaa 2 bbb 2 ccc 需要得到结果： id values ------ -------
如何把多行SQL或多行内容转为一行内容
最新发布
唯有热爱 可抵万难
08-27 283
链接：https://pan.quark.cn/s/b01284447533提取码：5BEc。
sql查询多行数据转成一行
qq_45914437的博客
05-23 4418
比如我们查询某学生各科成绩： SELECT s.sno,s.spassword,s.sname,c.cname,s.age,s.snative,s.health,s.hobby,k.kname,g.grade,r.rname from class c,grade g,kecheng k,student s,role r where s.sno=g.sno and g.kno=k.kno and s.cno=c.cno and r.rno=s.rno and s.sno=1 and spassword='12
Oracle 多行 转一行 case when
taoshi006的博客
07-31 536
create table TABLE_1 ( ID VARCHAR2(32 CHAR) not null, PRODUCT_NAME VARCHAR2(32 CHAR) ) create table TABLE_2 ( ID VARCHAR2(32 CHAR) not null, T1_ID VARCHAR2(32 CHAR), ...
主流数据库（SQL Server、Mysql、Oracle）通过sql实现多行数据合为一行
m0_59415345的博客
03-23 8901
FOR XML是 SQL Server 提供的一种功能，允许您将查询结果转换为 XML 格式。PATH模式则是其中一种灵活的方式来构造自定义的XML结构。： 当您想从单列中提取所有行的数据并连接成一个字符串列表时，可以使用。例如，假设有一个包含开发任务名称的rwmc这段sql执行的结果将会返回一个XML字符串，其中每个任务名称后面跟着逗号，所有名称连接在一起。这将返回每个部门名称都在元素内的XML结构。： 若要创建更复杂的嵌套结构，可以结合AS。
VB.NET源码 -一行多列转一列多行数据.txt
05-26
标题中的"一行多列转一列多行数据"就是一个典型的行列转换问题，它涉及到数据结构的转换和遍历。在这个案例中，提供的资源可能是一个包含VB.NET源代码的文本文件，用于解决此类问题。 首先，我们需要理解这个问题的...
动态行转列：处理不确定数量的行转列操作
创意程序员的博客
04-24 1911
行转列操作是一种常见的数据转换技术，它可以将原始的行数据转换为列数据，从而更方便地进行分析和可视化。然而，传统的行转列方式只能处理固定数量的行转列操作，无法处理不确定数量的行转列操作。为了解决这个问题，我们可以使用动态行转列技术。动态行转列可以处理不确定数量的行转列操作，因为它使用动态 SQL 语句生成动态的列名。动态行转列具有高度的灵活性和扩展性，可以适应不同的数据结构和应用场景。在本文中，我们将介绍如何使用动态 SQL 实现动态行转列，并提供一个具体的案例演示。
Sql Server 2000 行转列的实现（横排）
12-15
在SQL Server 2000中，当需要将表格数据从多行展示为单行，即将行转换为列时，可以使用动态SQL来实现。这种技术通常被称为“行转列”或“行列转换”。在本例中，我们将讨论如何通过案例来理解这个过程。 首先，我们...
sql 多行数据一行显示
05-15
多行数据一行显示多行数据一行显示多行数据一行显示多行数据一行显示
SQL实现将多行记录合并成一行
晓晓的天空
06-06 2万+
SQL实现将多行记录合并成一行
合并字段数据，多行转一行（2）
搬砖，砌墙，敲代码
08-09 1348
2010-10-11 17:49 SQL查询案例：多行转换为一行 使用通常的方式测试表与测试数据 CREATE TABLE TestTitle ( name   VARCHAR(10), titleVARCHAR(10) ); INSERT INTO TestTitle VALUES ('张三', '程序员'); INSERT INTO TestTitle VAL
sql server 多行数据合并一行显示
zgscwxd的博客
12-07 1999
sql server 多行数据合并一行显示
用SQL将多行字符串数据转换成一行数据(实例)
细节决定成败 点滴铸就辉煌
11-21 1271
SELECT * FROM ( SELECT * FROM ( SELECT o.OrderNo, case o.IsCustomerUrgent when 0 then N'不加急' when 1 then N'加急' END as IsCustomerUrgent FROM Orders o WHERE OrderDate BETWEEN '2015-
各数据库SQL查询结果多行数据合并成一行
热门推荐
qq_41512902的博客
07-08 4万+
各数据库SQL查询结果多行数据合并成一行
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值