一、數據庫SQL Server行轉列
需求：原始表的數據的結構如圖1所示，把相同的guid的code值轉換為列值。
（圖1）
目標：我們希望達到的效果如圖2所示，這裡的guid變成唯一的了，這行的記錄中包含了這個guid所對應的code字段值。
（圖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 (1, 1) NOT NULL,
[guid] [varchar] (50) NULL,
[code] [varchar] (50) NULL)
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 Server2005的row_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
（圖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，比如guid為91E92DCB-141A-30B2-E6CD-B59EABD21749的code值包括A,C,E,O,G這五個，我們可以想象guid為一個用戶，這五個code就是這個用戶訪問網站頁面的順序，那如果我想看到一個從A->C，C-> E，E-> O，O -> G這個矩陣中兩兩對應在數據庫中出現了多少次？
目標：我們希望達到的效果如圖4所示，這個圖可以解讀為A->A的個數是0，A->C的個數是1，D->F的個數是2，這些統計方法可能會在一些需要進行對比分析的過程中用到。
（圖4）
分析與實現：要實現圖4的效果，這就需要我們對數據進行矩陣轉換，下面我們來講講具體的實現：
1. 首先我們需要巧用left join和depth字段，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
（圖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]
（圖6）
3. 現在就需要把統計的A->A這樣數據的個數，並把它放入圖6中的表中，效果如圖7所示。
--統計
select code_from ,code_to, count(1) as 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]
（圖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]')
（圖8）
總結：其實這篇文章我想表達正是這個矩陣的生成方法，在現實中也許並不常用到，但是還是有些技巧性的東西在裡面，比如那個dept的用處；再比如a.[depth] = b.[depth]-1這個SQL的巧用；再比如行轉列解決矩陣問題；還有動態生成行轉列的SQL，其實這裡還可以使用SQL Server 2005的pivot來解決行轉列的問題，這裡就不列出來了。希望這篇文章能給你遇到的問題帶來一些幫助和啟示。
