sql server] 行轉列問題總結1 – 行轉列

行轉列問題總結 - 1、行轉列 （後面不斷整理論壇中出現的各類問題）

---1、最簡單的行轉列
/*

問題：假設有張學產生績表(tb)如下:
姓名 課程 分數
張三 語文 74
張三 數學 83
張三 物理 93
李四 語文 74
李四 數學 84
李四 物理 94

想變成(得到如下結果)：
姓名 語文 數學 物理
李四 74 84 94
張三 74 83 93
*/
--測試用
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
create table tb(姓名 varchar(10) , 課程 varchar(10) , 分數 int)
insert into tb values('張三' , '語文' , 74)
insert into tb values('張三' , '數學' , 83)
insert into tb values('張三' , '物理' , 93)
insert into tb values('李四' , '語文' , 74)
insert into tb values('李四' , '數學' , 84)
insert into tb values('李四' , '物理' , 94)
go

--SQL SERVER 2000 動態SQL,指課程不止語文、數學、物理這三門課程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 課程 when ''' + 課程 + ''' then 分數 else 0 end) [' + 課程 + ']'
from (select distinct 課程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql)
--通過動態構建@sql，得到如下指令碼
select 姓名 as 姓名 ,
max(case 課程 when '語文' then 分數 else 0 end) 語文,
max(case 課程 when '數學' then 分數 else 0 end) 數學,
max(case 課程 when '物理' then 分數 else 0 end) 物理
from tb
group by 姓名

--SQL SERVER 2005 動態SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + 課程 from tb group by 課程
set @sql = '[' + @sql + ']'
exec ('select * from (select * from tb) a pivot (max(分數) for 課程 in (' + @sql + ')) b')
--得到SQL SERVER 2005 靜態SQL。
select * from (select * from tb) a pivot (max(分數) for 課程 in (語文,數學,物理)) b

--查詢結果
/*
姓名 數學 物理 語文
---------- ----------- ----------- -----------
李四 84 94 74
張三 83 93 74

（所影響的行數為 2 行）
*/

--2 加合計
/*
問題：在上述結果的基礎上加平均分，總分，得到如下結果：
姓名 語文 數學 物理 平均分 總分
---- ---- ---- ---- ------ ----
李四 74 84 94 84.00 252
張三 74 83 93 83.33 250
*/

--SQL SERVER 2000 靜態SQL。
select 姓名 姓名,
max(case 課程 when '語文' then 分數 else 0 end) 語文,
max(case 課程 when '數學' then 分數 else 0 end) 數學,
max(case 課程 when '物理' then 分數 else 0 end) 物理,
cast(avg(分數*1.0) as decimal(18,2)) 平均分,
sum(分數) 總分
from tb
group by 姓名

--SQL SERVER 2000 動態SQL。
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 課程 when ''' + 課程 + ''' then 分數 else 0 end) [' + 課程 + ']'
from (select distinct 課程 from tb) as a
set @sql = @sql + ' , cast(avg(分數*1.0) as decimal(18,2)) 平均分 , sum(分數) 總分 from tb group by 姓名'
exec(@sql)

--SQL SERVER 2005 靜態SQL。
select m.* , n.平均分 , n.總分 from
(select * from (select * from tb) a pivot (max(分數) for 課程 in (語文,數學,物理)) b) m,
(select 姓名 , cast(avg(分數*1.0) as decimal(18,2)) 平均分 , sum(分數) 總分 from tb group by 姓名) n
where m.姓名 = n.姓名

--SQL SERVER 2005 動態SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 課程 from tb group by 課程
exec ('select m.* , n.平均分 , n.總分 from
(select * from (select * from tb) a pivot (max(分數) for 課程 in (' + @sql + ')) b) m ,
(select 姓名 , cast(avg(分數*1.0) as decimal(18,2)) 平均分 , sum(分數) 總分 from tb group by 姓名) n
where m.姓名 = n.姓名')

其他執行個體

http://topic.csdn.net/u/20100708/18/55df5a90-27a7-4452-a69a-27f735539a1f.html?seed=24842417&r=66831902#r_66831902

--3、不同資料按照序號轉為列，方法基本同 1

if object_id('tb1') is not null drop table tb1
go
CREATE table tb1 --資料表
(
cpici varchar(10) not null,
cname varchar(10) not null,
cvalue int null
)
--插入測試資料
INSERT INTO tb1 values('T501','x1',31)
INSERT INTO tb1 values('T501','x1',33)
INSERT INTO tb1 values('T501','x1',5)

INSERT INTO tb1 values('T502','x1',3)
INSERT INTO tb1 values('T502','x1',22)
INSERT INTO tb1 values('T502','x1',3)

INSERT INTO tb1 values('T503','x1',53)
INSERT INTO tb1 values('T503','x1',44)
INSERT INTO tb1 values('T503','x1',50)
INSERT INTO tb1 values('T503','x1',23)

--在sqlserver2000裡需要用自增輔助
alter table tb1 add id int identity
go
declare @s varchar(8000)
set @s='select cpici '
select @s=@s+',max(case when rn='+ltrim(rn)+' then cvalue end) as cvlue'+ltrim(rn)
from (select distinct rn from (select rn=(select count(1) from tb1 where cpici=t.cpici and id<=t.id) from tb1 t)a)t
set @s=@s+' from (select rn=(select count(1) from tb1 where cpici=t.cpici and id<=t.id),* from tb1 t
) t group by cpici'

exec(@s)
go
alter table tb1 drop column id

--再2005就可以用row_number
declare @s varchar(8000)
set @s='select cpici '
select @s=@s+',max(case when rn='+ltrim(rn)+' then cvalue end) as cvlue'+ltrim(rn)
from (select distinct rn from (select rn=row_number()over(partition by cpici order by getdate()) from tb1)a)t
set @s=@s+' from (select rn=row_number()over(partition by cpici order by getdate()),* from tb1
) t group by cpici'

exec(@s)

---結果
/*
cpici cvlue1 cvlue2 cvlue3 cvlue4
---------- ----------- ----------- ----------- -----------
T501 31 33 5 NULL
T502 3 22 3 NULL
T503 53 44 50 23
警告: 彙總或其他 SET 操作消除了空值。

(3 行受影響)

*/

--測試用
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
create table tb(電話號碼 varchar(15), 通話時間長度 int ,行業 varchar(10))
insert tb
select '13883633601', 10 ,'餐飲' union all
select '18689704236', 20 ,'物流' union all
select '13883633601', 20 ,'物流' union all
select '13883633601', 20 ,'汽車' union all
select '18689704236', 20 ,'醫學' union all
select '18689704236', 20 ,'it' union all
select '18689704236', 20 ,'汽車' union all
select '13883633601', 50 ,'餐飲'
go

declare @sql varchar(8000)
set @sql='select 電話號碼,sum(通話時間長度) 通話總和'
select @sql=@sql+',max(case when rowid='+ltrim(rowid)+' then 行業 else '''' end) as [行業'+ltrim(rowid)+']'
from (select distinct rowid from (select (select count(distinct 行業) from tb where 電話號碼=t.電話號碼 and 行業<=t.行業) rowid
from tb t) a) b
set @sql=@sql+' from ( select * , (select count(distinct 行業) from tb where 電話號碼=t.電話號碼 and 行業<=t.行業) rowid
from tb t ) t group by 電話號碼'
exec(@sql)

--結果
/*

（所影響的行數為 8 行）

電話號碼 通話總和 行業1 行業2 行業3 行業4
--------------- ----------- ---------- ---------- ---------- ----------
13883633601 100 餐飲 汽車 物流
18689704236 80 it 汽車 物流 醫學

（所影響的行數為 2 行）

*/

另一種動態行轉列:

http://topic.csdn.net/u/20100612/10/4CFCB667-89FA-4985-90D5-B8A420A6FF12.html

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([姓名] varchar(1),[部門] varchar(4),[學曆] varchar(4),[出生年月] datetime)
insert [tb]
select 'A','後勤','高中','1986-1-1' union all
select 'B','後勤','初中','1984-3-7' union all
select 'C','管理','本科','1987-2-1' union all
select 'D','操作','專科','1976-2-1' union all
select 'E','操作','專科','1943-2-1'
go

GO
if object_id('GetGroupByCol') is not null drop proc GetGroupByCol
go
create PROCEDURE [dbo].[GetGroupByCol]
@colm nvarchar(100)
AS
declare @sql varchar(4000)

set @sql='
declare @sql varchar(8000)
set @sql=''select 部門''
select @sql =@sql+ '', sum(case ltrim('+@colm+') when ''''''+ltrim(' + @colm + ')+'''''' then 1 else 0 end)
[''+ltrim(' + @colm + ')+'']'' from (select distinct '+@colm+' from tb where '+@colm+' is not null) as a
set @sql = @sql + '' from tb group by 部門''
exec(@sql)'

exec(@sql)
GO

exec GetGroupByCol N'學曆'
exec GetGroupByCol N'出生年月'
exec GetGroupByCol N'姓名'

/*

（所影響的行數為 5 行）

部門 本科 初中 高中 專科
---- ----------- ----------- ----------- -----------
操作 0 0 0 2
管理 1 0 0 0
後勤 0 1 1 0

（所影響的行數為 3 行）

部門 02 1 1943 12:00AM 02 1 1976 12:00AM 03 7 1984 12:00AM 01 1 1986 12:00AM 02 1 1987 12:00AM
---- ------------------ ------------------ ------------------ ------------------ ------------------
操作 1 1 0 0 0
管理 0 0 0 0 1
後勤 0 0 1 1 0

（所影響的行數為 3 行）

部門 A B C D E
---- ----------- ----------- ----------- ----------- -----------
操作 0 0 0 1 1
管理 0 0 1 0 0
後勤 1 1 0 0 0

（所影響的行數為 3 行）
*/

以下可參考的例子

1、普通多表聯合

http://topic.csdn.net/u/20100623/00/077055eb-784d-4b27-8407-2c17adc06c60.html?seed=81934135&r=66426155#r_66426155

http://topic.csdn.net/u/20100622/19/9710803c-441b-45d0-b010-703a2633fe89.html?47161

2、多表根據時間 計算序號
http://topic.csdn.net/u/20100623/12/bbb0921b-0e1b-4435-8e85-959d87844954.html?seed=2145286087&r=66438763#r_66438763
http://topic.csdn.net/u/20100701/09/1684649b-b893-463b-8b40-7f4b894cd41e.html?seed=205688256&r=66630774#r_66630774

3、財務相關
http://topic.csdn.net/u/20100626/00/83499112-43ae-4caa-a1fd-268cc5138da6.html?seed=415671352&r=66513615#r_66513615

4、根據行數轉列

http://topic.csdn.net/u/20100705/12/e325571b-c368-4174-859f-17ae708eca3d.html

http://topic.csdn.net/u/20100706/09/c34728dc-6167-45df-b7cf-974612b9aa8b.html

http://topic.csdn.net/u/20100706/16/f217deed-a2be-4950-b911-2624ac7a881a.html?39445

5、根據排序大小轉

http://topic.csdn.net/u/20100707/13/63f4a02e-ebc3-4c71-9380-d6b2ca0eb366.html?39970

6、分組排序按序號轉

http://topic.csdn.net/u/20100725/05/7f813114-c423-4759-97b8-b22e1e2e90d7.html?seed=471594449&r=67220945#r_67220945