`
zht1933
  • 浏览: 223054 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

SQL查询语句:将一列中多个不同的值放入一行的一个单元格里

阅读更多

表结构和数据如下(表名Test):

NO VALUE  NAME
1       a       测试1
1       b       测试2
1       c       测试3
1       d       测试4
2       e       测试5
4       f       测试6
4       g       测试7

Sql语句:
select No,
       ltrim(max(sys_connect_by_path(Value, ';')), ';') as Value,
       ltrim(max(sys_connect_by_path(Name, ';')), ';') as Name
  from (select No,
               Value,
               Name,
               rnFirst,
               lead(rnFirst) over(partition by No order by rnFirst) rnNext
          from (select a.No,
                       a.Value,
                       a.Name,
                       row_number() over(order by a.No, a.Value desc) rnFirst
                  from Test a) tmpTable1) tmpTable2
 start with rnNext is null
connect by rnNext = prior rnFirst
 group by No;

检索结果如下:

NO VALUE    NAME
1    a;b;c;d   测试1;测试2;测试3;测试4
2    e            测试5
4    f;g          测试6;测试7

简单解释一下那个Sql吧:
1、最内层的Sql(即表tmpTable1),按No和Value排序,并列出行号:
select a.No,
       a.Value,
       a.Name,
       row_number() over(order by a.No, a.Value desc) rnFirst
  from Test a

该语句结果如下:
NO VALUE NAME RNFIRST
1     d       测试4     1
1     c       测试3     2
1     b       测试2     3
1     a       测试1     4
2     e       测试5     5
4     g       测试7     6
4     f       测试6     7


2、外层的Sql(即表tmpTable2),根据No分区,取出当前行对应的下一条记录的行号字段:
select No,
       Value,
       Name,
       rnFirst,
       lead(rnFirst) over(partition by No order by rnFirst) rnNext
  from (这里是tmpTable1的SQL) tmpTable1

lead(rnFirst):取得下一行记录的rnFirst字段
over(partition by No order by rnFirst) 按rnFirst排序,并按No分区,分区就是如果下一行的No字段与当前行的No字段不相等时,不取下一行记录显示
该语句结果如下:
NO VALUE NAME RNFIRST RNNEXT
1     d        测试4     1         2
1     c        测试3     2         3
1     b        测试2     3         4
1     a        测试1     4         NULL
2     e        测试5     5         NULL
4     g        测试7     6         7
4     f        测试6     7         NULL


3、最后就是最外层的sys_connect_by_path函数与start递归了
sys_connect_by_path(Value, ';')
start with rnNext is null
connect by rnNext = prior rnFirst

这个大概意思就是从rnNext为null的那条记录开始,递归查找,
如果前一记录的rnFirst字段等于当前记录的rnNext字段,就把2条记录的Value用分号连接起来,
大家可以先试试下面这个没有Max和Group的Sql:
select No,
       sys_connect_by_path(Value, ';') as Value,
       sys_connect_by_path(Name, ';') as Name
  from (select No,
               Value,
               Name,
               rnFirst,
               lead(rnFirst) over(partition by No order by rnFirst) rnNext
          from (select a.No,
                       a.Value,
                       a.Name,
                       row_number() over(order by a.No, a.Value desc) rnFirst
                  from Test a) tmpTable1) tmpTable2
start with rnNext is null
connect by rnNext = prior rnFirst

结果是:
NO VALUE  NAME
1 ;a  ;测试1
1 ;a;b  ;测试1;测试2
1 ;a;b;c  ;测试1;测试2;测试3
1 ;a;b;c;d ;测试1;测试2;测试3;测试4
2 ;e  ;测试5
4 ;f  ;测试6
4 ;f;g  ;测试6;测试7

可以看到,每个No的最后一条记录就是我们要的了
所以在sys_connect_by_path外面套一个Max,再加个Group by No,得到的结果就是行转列的结果了
最后再加一个Ltrim,去掉最前面的那个分号,完成。

分享到:
评论
1 楼 aguai0 2012-06-05  
想用一下,但是有点看不懂

相关推荐

    SQLServer中如何将一个字段的多个记录值合在一行显示

    在本文中,我们将介绍如何在 SQL Server 中将一个字段的多个记录值合并到一行显示,并提供相关的示例代码和实现方法。 知识点 1:使用 User-Defined Function(UDF)实现字段值合并 在 SQL Server 中,我们可以...

    经典的SQL查询语句,将内容作为字段查询

    当我们谈论“经典的SQL查询语句,将内容作为字段查询”时,我们通常是指在SQL中使用内容作为查询的一部分来检索特定数据。这样的查询对于数据分析师、数据库管理员以及任何需要从大量数据中提取信息的人来说至关重要...

    将SQL数据表的一列转换为一行.docx

    SQL 数据表列转换为一行 在数据库管理中,经常会遇到将...这个示例展示了如何使用存储过程、临时表、动态 SQL 语句、 WHILE 循环、CALL 语句、ALTER TABLE 语句和 UPDATE 语句来将 SQL 数据表的一列转换为一行多列。

    SQL语句大全:珍藏版

    - SELECT语句:允许从一个或多个表中查询数据。使用SELECT语句时可以指定要检索的列、使用WHERE子句来设定查询条件、使用ORDER BY对结果进行排序等。 2. 数据操作语言(DML):用于对数据库中的数据进行增加、修改...

    SQLserver常用语句大全

    * SELECT 语句:从数据库表中检索数据行和列 * INSERT 语句:向数据库表添加新数据行 * DELETE 语句:从数据库表中删除数据行 * UPDATE 语句:更新数据库表中的数据 二、数据定义 * CREATE TABLE 语句:创建一个...

    用一条SQL语句将数据表中某列更新到另一个数据表里

    ### 使用单条SQL语句更新数据表中的某一列至另一表 #### 背景介绍 在数据库管理中,经常需要对数据进行更新操作来确保数据的准确性和时效性。有时候,这种更新不仅限于同一张表内,还可能涉及到跨表的操作。例如,...

    sql语句替换某列所有字段中的某个字符(如替换‘1,2,3,4’中的4)

    本文将深入探讨如何利用SQL语句实现对某一列所有字段中的特定字符进行替换的操作,这一过程通常用于数据清洗、格式调整或错误修正等场景。 ### SQL语句替换某列所有字段中的某个字符 #### 1. 基本概念与应用场景 ...

    SQL常用查询语句大全

    - 子查询:一个查询嵌套在另一个查询中,可以用于比较、过滤或者计算。 - 视图:视图是虚拟的表,基于一个或多个表的查询结果。它们提供了一种简化复杂查询和保护数据的方式。 - 分页查询:通过LIMIT和OFFSET...

    SQL语句语法

    - Subquery:子查询用于在一个查询中嵌套另一个查询。 - TRUNCATE:快速清空表,不记录单个行的删除。 - UPDATE:修改表中现有记录的值。 13.3 MySQL 实用工具语句: - DESCRIBE:显示表的结构信息。 - USE:切换到...

    sql列数不固定查询语句

    ### SQL列数不固定的查询语句解析与应用 #### 核心知识点概述 在SQL查询中,有时会遇到数据表的列数不固定的情况,即数据表中的某些列可能存在或缺失,这通常发生在需要根据不同的条件对数据进行聚合或者分组的...

    SQL查询语句生成器

    SQL查询语句生成器是一种工具,它帮助用户无需手动编写复杂的SQL命令,即可自动生成所需的查询语句。这种工具在数据库应用中极为重要,尤其对于那些不熟悉SQL语法或者希望提高工作效率的用户来说,它是宝贵的资源。...

    Sql语句实现表的行列转换,行转列,列转行

    例如,原始表可能按照行存储了不同类别的数据(如物品名和颜色),现在我们需要将其转换为每个类别单独成为一列的形式,便于后续的数据分析或展示。 #### 二、具体实现步骤 首先,根据提供的示例代码,我们创建了...

    SQL语句基础教程

    例如,要在Store_Information表格中找出所有不同的店名,可以使用以下SQL语句: SELECT DISTINCT store_name FROM Store_Information ### WHERE指令 WHERE指令让我们能够选择性地抓取资料。WHERE指令的语法结构...

    SQL查询语句大全集锦(超经典)

    联合查询(`UNION`)允许将两个或多个`SELECT`语句的结果集合并为一个结果集。默认情况下,`UNION`会自动去除重复的行。例如,要合并两个查询的结果: ```sql SELECT column_name(s) FROM table1 UNION SELECT ...

    arcgis中的sql语句

    ### ArcGIS中的SQL语句详解 #### 一、SQL在ArcGIS中的角色与应用 SQL,全称为Structured Query Language,即结构化查询语言,是专为数据库设计的一种标准语言,用于管理和操作数据库中的数据。在ArcGIS环境中,SQL...

    使用sql语句查询的数据按时间递增累加

    执行上述SQL语句后,每条记录都将包含一个额外的 `ۼԤ` 字段,显示截至该时间点的累计预计值。例如,对于第一条记录,“2013-01-01”的累计预计值为300;对于第二条记录,“2013-02-01”的累计预计值为380(即300+...

    access 一次执行多条sql语句

    本文将详细介绍如何在Access中实现一次执行多条SQL语句,并通过具体的代码示例来帮助理解这一过程。 #### 一、基础知识概述 1. **OLE DB**: OLE DB (Object Linking and Embedding, Database) 是一种用于访问各种...

    SQL 语句 将一个表中用特殊字符分割的字段转换成多行数据.docx

    在数据库中,经常会遇到将一个字段中的特殊字符分割的字符串转换成多行数据的情况。这是一个常见的需求,但是网上提供的解决方案往往非常复杂,难以理解和实现。为了解决这个问题,我们可以创建一个字符串分割函数,...

Global site tag (gtag.js) - Google Analytics