转载

将一列中多行相同的值只显示在一行

数据库环境：SQL SERVER 2008R2

需求如题，左图为初始数据，右图是已实现需求的数据展示

将一列中多行相同的值只显示在一行

简单说下我的实现思路

1.按id、name排序给原始数据生成行号

2.用递归判断上下行的id是否相等，第一次出现计数器初始值为1，后面再出现则计数器+1

3.对步骤2中生成的结果集再处理，计数器为1的id不变，计数器大于1则id为空字符串

我把实现的代码贴出来

/*准备基础数据*/ WITH    x0   AS ( SELECT   1 AS id ,         'a' AS NAME        UNION ALL        SELECT   1 AS id ,         'b' AS NAME        UNION ALL        SELECT   1 AS id ,         'c' AS NAME        UNION ALL        SELECT   2 AS id ,         'e' AS NAME        UNION ALL        SELECT   2 AS id ,         'd' AS NAME        UNION ALL        SELECT   3 AS id ,         'f' AS NAME        UNION ALL        SELECT   4 AS id ,         'h' AS NAME        UNION ALL        SELECT   4 AS id ,         'j' AS NAME      ),/*按id、name排序生成行号*/         x1   AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY id, name ) AS tid ,         CAST(id AS VARCHAR(2)) id ,         name        FROM     x0      ),/*递归设置计数器*/         x2 ( tid, id, name, level )   AS ( SELECT   tid ,         id ,         name ,         1 AS level        FROM     x1        WHERE    tid = 1        UNION ALL        SELECT   t1.tid ,         t1.id ,         t1.NAME ,         CASE WHEN t1.id = t2.id THEN level + 1      ELSE 1         END level        FROM     x1 t1         INNER JOIN x2 t2 ON t1.tid = t2.tid + 1      )     /*计数器为1则id不动，否则置为空字符串*/     SELECT  CASE WHEN level = 1 THEN id          ELSE ''     END id ,     name     FROM    x2

先比我的实现，有一网友提供了更简单的实现方式

我们来看一下他是怎么实现的

WITH    x0       AS ( SELECT   1 AS id ,     'a' AS NAME    UNION ALL    SELECT   1 AS id ,     'b' AS NAME    UNION ALL    SELECT   1 AS id ,     'c' AS NAME    UNION ALL    SELECT   2 AS id ,     'e' AS NAME    UNION ALL    SELECT   2 AS id ,     'd' AS NAME    UNION ALL    SELECT   3 AS id ,     'f' AS NAME    UNION ALL    SELECT   4 AS id ,     'h' AS NAME    UNION ALL    SELECT   4 AS id ,     'j' AS NAME    UNION ALL    SELECT   1 AS id ,     'j' AS NAME  ) SELECT  REPLACE(CASE WHEN ROW_NUMBER() OVER ( PARTITION BY CAST(ID AS VARCHAR(2)) ORDER BY NAME ) <> '1'      THEN 0      ELSE CAST(ID AS VARCHAR(20))         END, 0, '') AS ID ,         NAME FROM    x0