Sybase怎么将多行数据分组合并成一行多列javaweixin44159662的博客-

背景

题目比较抽象，具体解释一下。
有这么一张表，里面是多位客户在不同时间的不同状态。例如：

这里多行数据比较混乱，想将多行数据按照标识分组，再改为一行多列：

但是生产上往往不太需要这样的汇总，更多时候是希望汇总最近一次状态和上一次状态，如图：

那么具体要怎么实现呢？

心路历程

项目上线以后突然倍感压力，这几天休息的很差。然而项目上线并不算完，后续还需要对千万数据提数取数。
由于当初设计的时候是面向页面设计的，为客户crud方便考虑。但是后续领导告诉我还需要从数据库中提数生成报表。这一下给我弄得手忙脚乱。
一个java开发工作，上需要改前端页面，下需要搞数据提取，对于工作不足一年的我，真的有些难以接受。
抱怨许久，感慨万分。

解决方案

本来以为就是group_concat的事，没想到，sybase竟然不支持。只得手写存储过程。

步驟

1. 建表造数
   首先为了演示这个效果，我们根据以下语句建造数据来模拟这个过程。效果如图所示：
   
   代码如下：

CREATE TABLE dbo.FRIEND_LOG  (  ID_          NUMERIC (19) NOT NULL,  FRIEND_NAME_ VARCHAR (32) NOT NULL,  DATE_        VARCHAR (8) NULL,  STATE_       VARCHAR (4) NULL, CONSTRAINT PK_FRIEND_LOG PRIMARY KEY (ID_) ) GO  INSERT INTO dbo.FRIEND_LOG (ID_, FRIEND_NAME_, DATE_, STATE_) VALUES (1, '小老鼠', '20200428', '高兴') GO  INSERT INTO dbo.FRIEND_LOG (ID_, FRIEND_NAME_, DATE_, STATE_) VALUES (2, '小八戒', '20200429', '开心') GO  INSERT INTO dbo.FRIEND_LOG (ID_, FRIEND_NAME_, DATE_, STATE_) VALUES (3, '小笨喵', '20200501', '悲伤') GO  INSERT INTO dbo.FRIEND_LOG (ID_, FRIEND_NAME_, DATE_, STATE_) VALUES (4, '小老鼠', '20200502', '难受') GO  INSERT INTO dbo.FRIEND_LOG (ID_, FRIEND_NAME_, DATE_, STATE_) VALUES (5, '小老鼠', '20200503', '相思') GO  INSERT INTO dbo.FRIEND_LOG (ID_, FRIEND_NAME_, DATE_, STATE_) VALUES (6, '小八戒', '20200504', '怀旧') GO  INSERT INTO dbo.FRIEND_LOG (ID_, FRIEND_NAME_, DATE_, STATE_) VALUES (7, '小笨喵', '20200505', '头大') GO 

2. 将数据传到临时表
   为了不破坏上表的结构以及数据的完整性，我们需要将FRIEND_LOG表中需要的内容传至临时表中。在创建临时表前要先判断是否已经存在相同名字的临时表，若存在删除即可。然后临时表的结构分别为角色名、状态、所有状态、状态次数。
   首先，角色名、状态是FRIEND_LOG表中字段。
   ALL_STATE_字段的存在是因为我们需要把多行状态迁移到一行中，故我们需要一个字段来存储这些状态。
   而TIMES字段的设计目的则是为了记录各个角色的状态数量，具体功能下面会详解。
   当执行完这些语句后，效果如图：
   
   代码如下：

IF OBJECT_ID('#TEMP1') IS NOT NULL drop table #TEMP1   GO   SELECT   FRIEND_NAME_, STATE_, space(40) AS ALL_STATE_, 0 as TIMES  INTO #TEMP1 FROM FRIEND_LOG ORDER BY FRIEND_NAME_ , DATE_  DESC  

3. 使用计数法插入
   不要被标题吓跑。所谓计数法，也不过是十以内数字加减法。
   具体什么原理？
   其实很简单，无非是一条一条遍历数据，如果是第一次遇到这个角色，就直接将状态写入ALL_STATE_，TIMES记为1，count也记为1。如果是第n次，则一直将状态追加至ALL_STATE_，count也依次累加，而TIMES则为count+1。
   我们用效果图来解释一下：
   可以看到，第一次遍历小八戒，ALL_STATE_写入了“怀旧”状态，而TIMES为1，此时count也为1。然后我们第二次记录小八戒，此时状态追加了“开心”，TIMES为2，count也为2。后面皆以此类推即可。
   代码如下：

declare @state varchar(400) declare @id VARCHAR(32) declare @count int set @state='' set @count=0 update #TEMP1  set ALL_STATE_=(case when @id =FRIEND_NAME_ then @state||STATE_ else STATE_ end) ,@state=(case when @id =FRIEND_NAME_ then @state||STATE_ else STATE_ end) ,TIMES=(case when @id =FRIEND_NAME_ then @count+1 else 1 end) ,@count=(case when @id =FRIEND_NAME_ then @count+1 else 1 end) ,@id =FRIEND_NAME_ 

4. 分列查看
   最后一步了，我们对临时表的ALL_STATE_列进行分列查看皆可。通过substring函数，将其分到其他列。另外“(select FRIEND_NAME_, (case when max(TIMES) > 2 then 2 else max(TIMES) end)”语句是用取状态次数为2的，即最近两次状态，效果如图：
   此时代码如下：

select t.FRIEND_NAME_,substring(t.ALL_STATE_,1,2) state1,substring(t.ALL_STATE_,3,2) state2,,TIMES     from #TEMP1 t inner join (select FRIEND_NAME_, (case when max(TIMES) > 2 then 2 else max(TIMES) end) as tl from #TEMP1 group by FRIEND_NAME_) c   on t.FRIEND_NAME_=c.FRIEND_NAME_ and t.TIMES=c.tl 

另外，我们可以修改代码来取消限制，但是一定要确保正确分列，效果如图：
代码如下：

select t.FRIEND_NAME_,substring(t.ALL_STATE_,1,2) state1,substring(t.ALL_STATE_,3,2) state2,substring(t.ALL_STATE_,5,2) state3,TIMES     from #TEMP1 t inner join (select FRIEND_NAME_,  max(TIMES) as tl from #TEMP1 group by FRIEND_NAME_) c   on t.FRIEND_NAME_=c.FRIEND_NAME_ and t.TIMES=c.tl 

附录

将上述源码粘贴至同一sql文件即可测试运行，另外，如果土豪，也可以直接下载附件~
sql文件