time 
设为首页】【收藏本站
当前位置: 主页 > 数据库 > SQL Server > SQL 基础 > 简单但有用的SQL脚本Part6:特殊需要的行转列

简单但有用的SQL脚本Part6:特殊需要的行转列

时间:2010-08-31 22:37 点击:759次 字体:[ ]




一、数据库SQL Server行转列

需求:原始表的数据的结构如图1所示,把相同的guidcode值转换为列值。

简单但有用的SQL脚本Part6:特殊需要的行转列_www.fengfly.com

(图1

目标:我们希望达到的效果如图2所示,这里的guid变成唯一的了,这行的记录中包含了这个guid所对应的code字段值。

简单但有用的SQL脚本Part6:特殊需要的行转列_www.fengfly.com

(图2

分析与实现:要实现图1到图2的转变,这就是所谓的行转列,下面我们来讲讲具体的实现:

1.         首先我们先创建一个测试表,方便后面的效果展现;

--创建表
if exists (select * from sysobjects where id = OBJECT_ID('[TempTable_Base]'and OBJECTPROPERTY(id, 'IsUserTable'= 1
DROP TABLE [TempTable_Base]

CREATE TABLE [TempTable_Base] (
[id] [int]  IDENTITY (11)  NOT NULL,
[guid] [varchar]  (50NULL,
[code] [varchar]  (50NULL)

SET IDENTITY_INSERT [TempTable_Base] ON

INSERT [TempTable_Base] ([id],[guid],[code]VALUES ( 1,'91E92DCB-141A-30B2-E6CD-B59EABD21749','A')
INSERT [TempTable_Base] ([id],[guid],[code]VALUES ( 2,'91E92DCB-141A-30B2-E6CD-B59EABD21749','C')
INSERT [TempTable_Base] ([id],[guid],[code]VALUES ( 3,'91E92DCB-141A-30B2-E6CD-B59EABD21749','E')
INSERT [TempTable_Base] ([id],[guid],[code]VALUES ( 4,'91E92DCB-141A-30B2-E6CD-B59EABD21749','O')
INSERT [TempTable_Base] ([id],[guid],[code]VALUES ( 5,'91E92DCB-141A-30B2-E6CD-B59EABD21749','G')
INSERT [TempTable_Base] ([id],[guid],[code]VALUES ( 6,'79DD7AB9-CE57-9431-B020-DF99731FC99D','A')
INSERT [TempTable_Base] ([id],[guid],[code]VALUES ( 7,'79DD7AB9-CE57-9431-B020-DF99731FC99D','O')
INSERT [TempTable_Base] ([id],[guid],[code]VALUES ( 8,'79DD7AB9-CE57-9431-B020-DF99731FC99D','E')
INSERT [TempTable_Base] ([id],[guid],[code]VALUES ( 9,'79DD7AB9-CE57-9431-B020-DF99731FC99D','F')
INSERT [TempTable_Base] ([id],[guid],[code]VALUES ( 10,'79DD7AB9-CE57-9431-B020-DF99731FC99D','O')
INSERT [TempTable_Base] ([id],[guid],[code]VALUES ( 11,'79DD7AB9-CE57-9431-B020-DF99731FC99D','B')
INSERT [TempTable_Base] ([id],[guid],[code]VALUES ( 12,'79DD7AB9-CE57-9431-B020-DF99731FC99D','D')
INSERT [TempTable_Base] ([id],[guid],[code]VALUES ( 13,'79DD7AB9-CE57-9431-B020-DF99731FC99D','F')
INSERT [TempTable_Base] ([id],[guid],[code]VALUES ( 14,'D61651D9-1B0A-0362-EE91-A805AA3E08F2','O')
INSERT [TempTable_Base] ([id],[guid],[code]VALUES ( 15,'D61651D9-1B0A-0362-EE91-A805AA3E08F2','D')
INSERT [TempTable_Base] ([id],[guid],[code]VALUES ( 16,'D61651D9-1B0A-0362-EE91-A805AA3E08F2','F')
INSERT [TempTable_Base] ([id],[guid],[code]VALUES ( 17,'D61651D9-1B0A-0362-EE91-A805AA3E08F2','C')
INSERT [TempTable_Base] ([id],[guid],[code]VALUES ( 18,'D61651D9-1B0A-0362-EE91-A805AA3E08F2','U')
INSERT [TempTable_Base] ([id],[guid],[code]VALUES ( 19,'D61651D9-1B0A-0362-EE91-A805AA3E08F2','F')
INSERT [TempTable_Base] ([id],[guid],[code]VALUES ( 20,'4802F0CD-B53F-A3F5-1C78-2D7424579C06','A')
INSERT [TempTable_Base] ([id],[guid],[code]VALUES ( 21,'3CCBFF9F-827B-6639-4780-DA7215166728','O')
INSERT [TempTable_Base] ([id],[guid],[code]VALUES ( 22,'3CCBFF9F-827B-6639-4780-DA7215166728','M')
INSERT [TempTable_Base] ([id],[guid],[code]VALUES ( 23,'3CCBFF9F-827B-6639-4780-DA7215166728','C')
INSERT [TempTable_Base] ([id],[guid],[code]VALUES ( 24,'3CCBFF9F-827B-6639-4780-DA7215166728','M')

SET IDENTITY_INSERT [TempTable_Base] OFF

SELECT * FROM [TempTable_Base]

 

2.         使用SQL Server2005row_number()函数来进行分组排名,把同一个guid的数据进行标识,这样就可以让不同的guid具有数字1以后的数值,这样就可以作为新的列名来进行行转列了,执行下面的SQL就可以达到图3所示的效果了。 

--对数据进行分组
select base.*,row_number() over (partition by guid order by ID) as depth 
from [TempTable_Base] as base
order by id

简单但有用的SQL脚本Part6:特殊需要的行转列_www.fengfly.com

(图3

 

3.         生成分组表,把上面的结构插入到一张新的表中,把数据保存为一个表是为了方便后面的统计使用。 

--生成分组表
select base.*,row_number() over (partition by guid order by ID) as depth 
into [TempTable_Depth]
from [TempTable_Base] as base
order by id

SELECT * FROM [TempTable_Depth]

 

4.         动态行转列的SQL,因为你不知道列数据会有多少,所以我们使用下面的SQL来动态的生成列名,结果就如图2所示。

--行转列
declare @s nvarchar(max)
set @s=''
Select @s=@s+','+quotename([depth])+'=max(case when [depth]='+quotename([depth],'''')+' then code else null end)'
from [TempTable_Depth] group by [depth] order by [depth]
exec('select guid '+@s+' from [TempTable_Depth] group by guid order by guid')

 

 

二、数据库SQL Server生成矩阵数据

需求:我们回过头来看看图1,比如guid91E92DCB-141A-30B2-E6CD-B59EABD21749code值包括A,C,E,O,G这五个,我们可以想象guid为一个用户,这五个code就是这个用户访问网站页面的顺序,那如果我想看到一个从A->CC-> EE-> OO -> G这个矩阵中两两对应在数据库中出现了多少次?

 

目标:我们希望达到的效果如图4所示,这个图可以解读为A->A的个数是0A->C的个数是1D->F的个数是2,这些统计方法可能会在一些需要进行对比分析的过程中用到。

简单但有用的SQL脚本Part6:特殊需要的行转列_www.fengfly.com

(图4

分析与实现:要实现图4的效果,这就需要我们对数据进行矩阵转换,下面我们来讲讲具体的实现:

1.         首先我们需要巧用left joindepth字段,depth就相当于用户访问的顺序,所以我们可以把之前有序的拷贝一份到右边去,效果如图5所示。

--巧用左连接和depth字段进行处理
select a.id,a.guid,a.code as code_from,b.code as code_to 
from [TempTable_Depth] as a
left join [TempTable_Depth] as b
on a.guid = b.guid and a.[depth] = b.[depth]-1

简单但有用的SQL脚本Part6:特殊需要的行转列_www.fengfly.com

(图5

 

2.         为了方便统计,把上面的成果保存到一个表中,之后就是为所有唯一的code值生成一个以code作为元素的二维矩阵的原始数据,它就相当于一个临时表一样,用来保存每一个code对应的个数,效果如图6所示。 

--生成From和To的对应关系
select a.id,a.guid,a.code as code_from,b.code as code_to 
into [TempTable_FromTo]
from [TempTable_Depth] as a
left join [TempTable_Depth] as b
on a.guid = b.guid and a.[depth] = b.[depth]-1


--生成矩阵
select distinct code_from ,1 as num
into [TempTable_t1]
from [TempTable_FromTo]
order by code_from

select * from [TempTable_t1]

--生成一个矩阵表
select a.code_from ,b.code_from as code_to,0 as counts  
into [TempTable_t2]
from [TempTable_t1] as a
left join [TempTable_t1] as b
on a.num = b.num
order by a.code_from ,b.code_from

select * from [TempTable_t2]

简单但有用的SQL脚本Part6:特殊需要的行转列_www.fengfly.com

(图6

 

3.         现在就需要把统计的A->A这样数据的个数,并把它放入图6中的表中,效果如图7所示。 

--统计
select code_from ,code_to, count(1as counts
into [TempTable_t3]
from [TempTable_FromTo]
where code_to is not null
group by code_from ,code_to--,guid
order by code_from ,code_to

select * from [TempTable_t3]


--更新统计的数字
update a set a.counts = b.counts
from [TempTable_t2] as a,[TempTable_t3] as b
where a.code_from = b.code_from 
and a.code_to = b.code_to
and b.counts is not null

select * from [TempTable_t2]

简单但有用的SQL脚本Part6:特殊需要的行转列_www.fengfly.com

(图7

 

4.         现在需要做的就是把行转列了,这也没有违背我们的标题啊,呵呵,效果如图8所示。 

--查询矩阵
declare @s nvarchar(max)
set @s=''
Select @s=@s+','+quotename([code_to])+'=max(case when [code_to]='+quotename([code_to],'''')+' then counts else null end)'
from [TempTable_t2] group by [code_to] order by [code_to]
exec('select [code_from] '+@s+' from [TempTable_t2] group by [code_from] order by [code_from]')

 

简单但有用的SQL脚本Part6:特殊需要的行转列_www.fengfly.com
 

(图8

 

总结:其实这篇文章我想表达正是这个矩阵的生成方法,在现实中也许并不常用到,但是还是有些技巧性的东西在里面,比如那个dept的用处;再比如a.[depth] = b.[depth]-1这个SQL的巧用;再比如行转列解决矩阵问题;还有动态生成行转列的SQL,其实这里还可以使用SQL Server 2005pivot来解决行转列的问题,这里就不列出来了。希望这篇文章能给你遇到的问题带来一些帮助和启示。

 

转自:http://www.cnblogs.com/gaizai/archive/2010/08/12/1797346.html



本文地址 : http://www.fengfly.com/plus/view-187432-1.html
标签: sql脚本 特殊需要 行转列
------分隔线----------------------------
最新评论 查看所有评论
发表评论 查看所有评论
请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
评价:
表情:
验证码: