同组合并成列问题

同组合并成列问题

 

同学问了我将数据库的表同组合并成列的问题，我第一反映是建一个二维数组，两个HASH表分别对应行和列，逐行读取累加，但后来说是要在SQL里直接完成返回结果集，SQL是我最弱的，于是趁此问题也好好地学习了一下，使用方法较水，希望可以得到指点。

 

一．问题描述

问题描述可以简单总结为：现有一个初始表（名称、分组、值），求根据“名称成行、分组成列、值累加”的名称、分组不重复结果集。

原表名为：JqTestTable  表内容如下

ID	Name	GroupName	Value
1	123	A	1
2	123	B	2
3	123	A	3
4	123	B	4
5	456	A	5
6	456	B	6
7	456	A	7
8	456	B	8

 

         现要求得到的结果集为：

Name	A	B
123	4	6
456	12	14

        

二．解题思路

那么算法可以总结为这么几个步骤

1、得到所有分组（不重复）

2、根据分组创建临时表

3、把原始数据经算法插入到临时表

  a、先将初始表根据同名、同组、合并累加

  b、遍历得到的结果、按需求插入到对应的临时表中

4、查询临时表得到结果集

5、删除临时表

 

三．解题过程

1.   得到所有分组（不重复）

得到所有分组， 就是获取初始表的所以有不重复的分组字段的记录，如下：

A int default0,  B int default 0 ……

int为整型、default 0为初始值

去复得分组的语句为：select distinctGroupName from JqTestTable

根据得到的结果，循环遍历得到各个分组，循环内语句如下：

select distincttop (@i + 1) GroupName from JqTestTable where GroupName not in

         (select distinct top (@i) GroupNamefrom JqTestTable)

建议最好用游标完成遍历过程

这样得到的就是每个列的值，再组织一下，成：Aint default 0,  B int default 0 ……

（注：这里由于组织出来的列名的生成出来的，所以要用到动态SQL语句）

2.   根据分组创建临时表

根据所有分组可以生成新建表的语句

create tableJqTempTable (Name varchar(20),  A intdefault 0,  B int default 0)

得到空表如下：

Name	A	B
NULL	NULL	NULL

 

3.   把原始数据经算法插入到临时表

A.      先将初始表根据同名、同组、合并累加

算法语句为：

select  name, groupname,sum(value) as 'Sum'
from JqTestTable group by groupname, name

得到的结果为：

Name	GroupName	Value
123	A	4
123	B	6
456	A	12
456	B	14

这样去重之后只需要重新组织一下表的结构就可以了

 

B.     遍历得到的结果、按需求插入到对应的临时表中

这里应该是最核心的操作步骤了，方法为：

比如 我们遍历上表得到的第一个值是123，由于空的临时表没有123这个NAME的记录，所以我们要先插入一个记录，算法如下：

-- 如果不存在则插入，如果存在则修改这列

if @strName notin (select name from JqTempTable)

begin

INSERT INTOJqTempTable(Name) VALUES (@strName)

end

以上的操作完成之后肯定是有123的记录了，那么我们得到的第二个值是A, 第三个值是1,那么我们只要在NAME为123的这个记录里插入（或累加）1到列 = A的项中

语句表达为：

updateJqTempTable set A = A + 4 where Name = 123

接下来的语句分别为：

updateJqTempTable set B = B + 6 where Name = 123

updateJqTempTable set A = A + 12 where Name = 456

updateJqTempTable set B = B + 14 where Name = 456

当然，这些语句不是自己打上去的，是用动态SQL去生成的

生成的原代码的语句为：

set @strSql ='update JqTempTable set ' + @strGroupName + ' = ' + @strGroupName + ' + ' +@strValue  + ' where Name = ' + @strName;

exec(@strSql)

然后我们就得到临时表了：

Name	A	B
123	4	6
456	12	14

 

4.   查询临时表得到结果集

这个临时表也就是需要的结果，我们只要把它查询出来就可以了：

select * fromJqTempTable

Name	A	B
123	4	6
456	12	14

 

5.   把原始数据经算法插入到临时表

得到结果之后，最后别忘了删除临时表：

drop table JqTempTable

 

四．具体代码

 
-- 得到列名集
declare @strSql varchar(Max)
declare @strColumnAdd varchar(Max)
declare @i int
declare @nCount int
set @i = 0
set @nCount = (select count(distinctGroupName) from JqTestTable)
set @strColumnAdd = '
while @i < @nCount
begin
         set@strColumnAdd = @strColumnAdd + ' ' +
         (
                   selectdistinct top (@i + 1) GroupName from JqTestTable where GroupName not in
                   (selectdistinct top (@i) GroupName from JqTestTable)
         )+ ' int default 0'
         set@i = @i + 1
         if(@i < @nCount)
                   set@strColumnAdd = @strColumnAdd + ', '
--       print@strColumnAdd
end
 
--  新建一个临时表
set @strSql = 'create table JqTempTable(Name varchar(20), ' + @strColumnAdd + ')'
--print @strSql
 
if object_id('JqTempTable') is not null -- 判断表是否存在
         droptable JqTempTable
        
exec(@strSql)
 
 
--  处理数据
 
declare @strName varchar(20)
declare @strGroupName varchar(20)
declare @strValue varchar(20)
declare @strTest varchar(100)
 
-- 判断游标是否存在
if (cursor_status('global','cursorJqTest')<> -3) and (cursor_status('local','cursorJqTest') <> -3)
begin
         closecursorJqTest
         deallocatecursorJqTest
end
 
declare cursorJqTest cursor for
(select name, groupname, sum(value) as 'Sum' from JqTestTable group bygroupname, name)
open cursorJqTest
fetch next from cursorJqTest into @strName,@strGroupName, @strValue
while(@@fetch_status = 0)
begin
         set@strTest = @strName + ' ' + @strGroupName + ' ' + @strValue
--       print@strTest
         --如果不存在则插入，如果存在则修改这列
         if@strName not in (select name from JqTempTable)
         begin
                   INSERTINTO JqTempTable(Name) VALUES (@strName)       
         end
         --累加列值
         set@strSql = 'update JqTempTable set ' + @strGroupName + ' = ' + @strGroupName + '+ ' + @strValue 
                                     +' where Name = ' + @strName;
--       print@strSql
         exec(@strSql)
 
         fetchnext from cursorJqTest into @strName, @strGroupName, @strValue
end
close cursorJqTest
deallocate cursorJqTest
 
select * from JqTempTable
 
drop table JqTempTable
 

五．附录

1.      判断表是否存在

if object_id('JqTempTable') is not null

         droptable JqTempTable

 

2.      判断游标是否存在

if (cursor_status('global','cursorJqTest')<> -3) and (cursor_status('local','cursorJqTest') <> -3)

begin

         closecursorJqTest

         deallocatecursorJqTest

end

 

3.      游标使用：

 

-- 列表中声明的变量数目必须与所选列的数目相同(重要)

declare @a varchar(20)

declare @b varchar(20)

declare @c varchar(20)

declare @d varchar(20)

declare @e varchar(100)

 

declare cursorJqTest cursor for select *from JqTestTable

open cursorJqTest

fetch next from cursorJqTest into @a, @b,@c, @d

while(@@fetch_status=0)--返回被 FETCH 语句执行的最后游标的状态，而不是任何当前被连接打开的游标的状态。

begin

         set@e = @a + ' ' + @b + ' ' + @c + ' ' + @d

         print@e

         fetchnext from cursorJqTest into @a, @b, @c, @d

end

close cursorJqTest--关闭游标

deallocate cursorJqTest--删除游标