行變列
表 byf10
858,2F000858,36193845,2006-03-16 00:00:00.000,2,5210216,訊網有限公司,CHRISTINE,35292970,,5210,05,1,,深圳市新達實業有限公司,蔣先生,0755-83490151,,5125,00,1,30.00,1.0340,1.0305,HKD,161.00,1,.00,.00,.00,161.00,161.00,RMB,166,1,.00,.00,135.00,135.00,0,5210,蘇蓓,2006-03-14 10:44:39.000
表byf01
2006-03-16 00:00:00.000,2006-03-16 00:00:00.000,RMB,NTD,3.9400,陳佳琦 ,1,2006-03-16 09:25:31.000,3.9400,1.0010,1126,.0200,NULL
2006-03-16 00:00:00.000,2006-03-16 00:00:00.000,RMB,HKD,.9540,陳佳琦 ,1,2006-03-16 09:43:31.000,.9540,.0010,1127,.0020,NULL
2006-03-16 00:00:00.000,2006-03-16 00:00:00.000,NTD,RMB,.2410,陳佳琦 ,1,2006-03-16 09:59:31.000,.2410,.0010,1128,.0020,NULL
2006-03-16 00:00:00.000,2006-03-16 00:00:00.000,NTD,HKD,.2340,陳佳琦 ,1,2006-03-16 09:12:32.000,.2340,.0010,1129,.0020,NULL
2006-03-16 00:00:00.000,2006-03-16 00:00:00.000,HKD,RMB,1.0340,陳佳琦 ,1,2006-03-16 09:31:32.000,1.0340,1.0010,1130,.0020,NULL
2006-03-16 00:00:00.000,2006-03-16 00:00:00.000,HKD,NTD,4.1200,陳佳琦 ,1,2006-03-16 09:47:32.000,4.1200,1.0010,1131,.0200,NULL
2006-03-16 00:00:00.000,2006-03-16 00:00:00.000,RMB,NTD,3.9400,陳佳琦 ,1,2006-03-16 09:25:31.000,3.9400,1.0010,1126,.0200,NULL
2006-03-16 00:00:00.000,2006-03-16 00:00:00.000,RMB,HKD,.9540,陳佳琦 ,1,2006-03-16 09:43:31.000,.9540,.0010,1127,.0020,NULL
2006-03-16 00:00:00.000,2006-03-16 00:00:00.000,NTD,RMB,.2410,陳佳琦 ,1,2006-03-16 09:59:31.000,.2410,.0010,1128,.0020,NULL
2006-03-16 00:00:00.000,2006-03-16 00:00:00.000,NTD,HKD,.2340,陳佳琦 ,1,2006-03-16 09:12:32.000,.2340,.0010,1129,.0020,NULL
2006-03-16 00:00:00.000,2006-03-16 00:00:00.000,HKD,RMB,1.0340,陳佳琦 ,1,2006-03-16 09:31:32.000,1.0340,1.0010,1130,.0020,NULL
2006-03-16 00:00:00.000,2006-03-16 00:00:00.000,HKD,NTD,4.1200,陳佳琦 ,1,2006-03-16 09:47:32.000,4.1200,1.0010,1131,.0200,NULL
寫個SQL語句得到下面的結果
2F000858,5210,5125,HKD,161.00,RMB,.00,135.00,NULL,4.1200,3.9400
二種方法：
第一種方法：
/* 查找出收款幣別轉付款幣別對應的及時匯率*/
create view byf10_b10_30b10_40view as
select DISTINCT byf10.B10_01 as b10_30b10_40B10_01,byf01.B01_05 as b10_30b10_40B01_05,byf10.B10_09,byf10.B10_17,byf10.B10_30,byf10.B10_31,byf10.B10_40,byf10.B10_43,byf10.B10_46
from byf10
left join byf01
on byf10.B10_30+byf10.B10_40=byf01.B01_02+byf01.B01_03
where byf01.B01_01<=byf10.b10_02 and byf10.b10_02<=byf01.B01_011
GO
/* 查找出收款幣別轉臺幣匯率*/
create view byf10_b10_30NTDview as
select DISTINCT byf10.B10_01 as b10_30NTDB10_01,byf01.B01_04 as b10_30NTDB01_04
from byf10
left join byf01
on byf10.B10_30+'NTD'=byf01.B01_02+byf01.B01_03
where byf01.B01_01<=byf10.b10_02 and byf10.b10_02<=byf01.B01_011
GO
/* 查找出付款幣別轉臺幣匯率*/
create view byf10_b10_40NTDview as
select DISTINCT byf10.B10_01 as b10_40NTDB10_01,byf01.B01_04 as b10_40NTDB01_04
from byf10
left join byf01
on byf10.B10_40+'NTD'=byf01.B01_02+byf01.B01_03
where byf01.B01_01<=byf10.b10_02 and byf10.b10_02<=byf01.B01_011
GO
/* 將所有查找結果合并成一張表*/
select byf10_b10_30b10_40view.b10_30b10_40B10_01,byf10_b10_30b10_40view.B10_09,byf10_b10_30b10_40view.B10_17,byf10_b10_30b10_40view.B10_30,byf10_b10_30b10_40view.B10_31,byf10_b10_30b10_40view.B10_40,byf10_b10_30b10_40view.B10_43,byf10_b10_30b10_40view.B10_46,byf10_b10_30b10_40view.b10_30b10_40B01_05,byf10_b10_30NTDview.b10_30NTDB01_04, byf10_b10_40NTDview.b10_40NTDB01_04
from byf10_b10_30b10_40view
left join byf10_b10_30NTDview
on byf10_b10_30b10_40view.b10_30b10_40B10_01=byf10_b10_30NTDview.b10_30NTDB10_01
left join byf10_b10_40NTDview
on byf10_b10_30b10_40view.b10_30b10_40B10_01=byf10_b10_40NTDview.b10_40NTDB10_01
GO
drop view byf10_b10_30NTDview
drop view byf10_b10_40NTDview
drop view byf10_b10_30b10_40view
GO
第二種方法：
select a.b10_30b10_40B10_01,a.B10_09,a.B10_17,a.B10_30,a.B10_31,a.B10_40,a.B10_43,a.B10_46,a.b10_30b10_40B01_05,b.b10_30NTDB01_04, c.b10_40NTDB01_04
from
(select DISTINCT byf10.B10_01 as b10_30b10_40B10_01,byf01.B01_05 as b10_30b10_40B01_05,byf10.B10_09,byf10.B10_17,byf10.B10_30,byf10.B10_31,byf10.B10_40,byf10.B10_43,byf10.B10_46
from byf10
left join byf01
on byf10.B10_30+byf10.B10_40=byf01.B01_02+byf01.B01_03
where byf01.B01_01<=byf10.b10_02 and byf10.b10_02<=byf01.B01_011)
as a
left join
(select DISTINCT byf10.B10_01 as b10_30NTDB10_01,byf01.B01_04 as b10_30NTDB01_04
from byf10
left join byf01
on byf10.B10_30+'NTD'=byf01.B01_02+byf01.B01_03
where byf01.B01_01<=byf10.b10_02 and byf10.b10_02<=byf01.B01_011)
as b
on a.b10_30b10_40B10_01=b.b10_30NTDB10_01
left join
(select DISTINCT byf10.B10_01 as b10_40NTDB10_01,byf01.B01_04 as b10_40NTDB01_04
from byf10
left join byf01
on byf10.B10_40+'NTD'=byf01.B01_02+byf01.B01_03
where byf01.B01_01<=byf10.b10_02 and byf10.b10_02<=byf01.B01_011)
as c
on a.b10_30b10_40B10_01=c.b10_40NTDB10_01
GO
創建表：
CREATE TABLE [byf01] (
[B01_01] [datetime] NULL ,
[B01_011] [datetime] NULL ,
[B01_02] [nvarchar] (3) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B01_03] [nvarchar] (3) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B01_04] [decimal](6, 4) NULL ,
[bName] [char] (10) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B01_06] [bit] NULL ,
[bdate] [datetime] NULL ,
[B01_07] [decimal](18, 4) NULL ,
[B01_08] [decimal](18, 4) NULL ,
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[B01_Span] [decimal](6, 4) NULL ,
[B01_05] [decimal](6, 4) NULL
) ON [PRIMARY]
GO
CREATE TABLE [byf10] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[B10_01] [nvarchar] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B10_011] [nvarchar] (10) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B10_02] [datetime] NULL ,
[B10_03] [nvarchar] (1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B10_04] [nvarchar] (7) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B10_05] [nvarchar] (40) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B10_06] [nvarchar] (25) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B10_07] [nvarchar] (16) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B10_08] [nvarchar] (30) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B10_09] [nvarchar] (4) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B10_10] [nvarchar] (2) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B10_11] [bit] NULL ,
[B10_12] [nvarchar] (7) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B10_13] [nvarchar] (40) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B10_14] [nvarchar] (25) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B10_15] [nvarchar] (16) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B10_16] [nvarchar] (30) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B10_17] [nvarchar] (4) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B10_18] [nvarchar] (2) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B10_19] [bit] NULL ,
[B10_21] [numeric](8, 2) NULL ,
[B10_22] [numeric](8, 4) NULL ,
[B10_23] [numeric](8, 4) NULL ,
[B10_30] [nvarchar] (3) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B10_31] [numeric](8, 2) NULL ,
[B10_32] [nvarchar] (1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B10_33] [decimal](8, 2) NULL ,
[B10_34] [decimal](8, 2) NULL ,
[B10_35] [decimal](8, 2) NULL ,
[B10_36] [decimal](8, 2) NULL ,
[B10_37] [decimal](8, 2) NULL ,
[B10_40] [nvarchar] (3) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B10_41] [decimal](8, 0) NULL ,
[B10_42] [nvarchar] (1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B10_43] [decimal](8, 2) NULL ,
[B10_45] [decimal](8, 2) NULL ,
[B10_46] [decimal](8, 2) NULL ,
[B10_47] [decimal](8, 2) NULL ,
[del] [bit] NULL ,
[comID] [nvarchar] (4) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[bName] [nvarchar] (10) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[bdate] [datetime] NULL ,
[B10_48] [decimal](6, 4) NULL ,
[B10_49] [decimal](6, 4) NULL ,
[B10_50] [decimal](6, 4) NULL
) ON [PRIMARY]
GO
858,2F000858,36193845,2006-03-16 00:00:00.000,2,5210216,訊網有限公司,CHRISTINE,35292970,,5210,05,1,,深圳市新達實業有限公司,蔣先生,0755-83490151,,5125,00,1,30.00,1.0340,1.0305,HKD,161.00,1,.00,.00,.00,161.00,161.00,RMB,166,1,.00,.00,135.00,135.00,0,5210,蘇蓓,2006-03-14 10:44:39.000
表byf01
2006-03-16 00:00:00.000,2006-03-16 00:00:00.000,RMB,NTD,3.9400,陳佳琦 ,1,2006-03-16 09:25:31.000,3.9400,1.0010,1126,.0200,NULL
2006-03-16 00:00:00.000,2006-03-16 00:00:00.000,RMB,HKD,.9540,陳佳琦 ,1,2006-03-16 09:43:31.000,.9540,.0010,1127,.0020,NULL
2006-03-16 00:00:00.000,2006-03-16 00:00:00.000,NTD,RMB,.2410,陳佳琦 ,1,2006-03-16 09:59:31.000,.2410,.0010,1128,.0020,NULL
2006-03-16 00:00:00.000,2006-03-16 00:00:00.000,NTD,HKD,.2340,陳佳琦 ,1,2006-03-16 09:12:32.000,.2340,.0010,1129,.0020,NULL
2006-03-16 00:00:00.000,2006-03-16 00:00:00.000,HKD,RMB,1.0340,陳佳琦 ,1,2006-03-16 09:31:32.000,1.0340,1.0010,1130,.0020,NULL
2006-03-16 00:00:00.000,2006-03-16 00:00:00.000,HKD,NTD,4.1200,陳佳琦 ,1,2006-03-16 09:47:32.000,4.1200,1.0010,1131,.0200,NULL
2006-03-16 00:00:00.000,2006-03-16 00:00:00.000,RMB,NTD,3.9400,陳佳琦 ,1,2006-03-16 09:25:31.000,3.9400,1.0010,1126,.0200,NULL
2006-03-16 00:00:00.000,2006-03-16 00:00:00.000,RMB,HKD,.9540,陳佳琦 ,1,2006-03-16 09:43:31.000,.9540,.0010,1127,.0020,NULL
2006-03-16 00:00:00.000,2006-03-16 00:00:00.000,NTD,RMB,.2410,陳佳琦 ,1,2006-03-16 09:59:31.000,.2410,.0010,1128,.0020,NULL
2006-03-16 00:00:00.000,2006-03-16 00:00:00.000,NTD,HKD,.2340,陳佳琦 ,1,2006-03-16 09:12:32.000,.2340,.0010,1129,.0020,NULL
2006-03-16 00:00:00.000,2006-03-16 00:00:00.000,HKD,RMB,1.0340,陳佳琦 ,1,2006-03-16 09:31:32.000,1.0340,1.0010,1130,.0020,NULL
2006-03-16 00:00:00.000,2006-03-16 00:00:00.000,HKD,NTD,4.1200,陳佳琦 ,1,2006-03-16 09:47:32.000,4.1200,1.0010,1131,.0200,NULL
寫個SQL語句得到下面的結果
2F000858,5210,5125,HKD,161.00,RMB,.00,135.00,NULL,4.1200,3.9400
二種方法：
第一種方法：
/* 查找出收款幣別轉付款幣別對應的及時匯率*/
create view byf10_b10_30b10_40view asselect DISTINCT byf10.B10_01 as b10_30b10_40B10_01,byf01.B01_05 as b10_30b10_40B01_05,byf10.B10_09,byf10.B10_17,byf10.B10_30,byf10.B10_31,byf10.B10_40,byf10.B10_43,byf10.B10_46from byf10 left join byf01 on byf10.B10_30+byf10.B10_40=byf01.B01_02+byf01.B01_03 where byf01.B01_01<=byf10.b10_02 and byf10.b10_02<=byf01.B01_011GO/* 查找出收款幣別轉臺幣匯率*/create view byf10_b10_30NTDview asselect DISTINCT byf10.B10_01 as b10_30NTDB10_01,byf01.B01_04 as b10_30NTDB01_04from byf10 left join byf01 on byf10.B10_30+'NTD'=byf01.B01_02+byf01.B01_03 where byf01.B01_01<=byf10.b10_02 and byf10.b10_02<=byf01.B01_011GO/* 查找出付款幣別轉臺幣匯率*/create view byf10_b10_40NTDview asselect DISTINCT byf10.B10_01 as b10_40NTDB10_01,byf01.B01_04 as b10_40NTDB01_04from byf10 left join byf01 on byf10.B10_40+'NTD'=byf01.B01_02+byf01.B01_03 where byf01.B01_01<=byf10.b10_02 and byf10.b10_02<=byf01.B01_011GO/* 將所有查找結果合并成一張表*/select byf10_b10_30b10_40view.b10_30b10_40B10_01,byf10_b10_30b10_40view.B10_09,byf10_b10_30b10_40view.B10_17,byf10_b10_30b10_40view.B10_30,byf10_b10_30b10_40view.B10_31,byf10_b10_30b10_40view.B10_40,byf10_b10_30b10_40view.B10_43,byf10_b10_30b10_40view.B10_46,byf10_b10_30b10_40view.b10_30b10_40B01_05,byf10_b10_30NTDview.b10_30NTDB01_04, byf10_b10_40NTDview.b10_40NTDB01_04from byf10_b10_30b10_40viewleft join byf10_b10_30NTDview on byf10_b10_30b10_40view.b10_30b10_40B10_01=byf10_b10_30NTDview.b10_30NTDB10_01 left join byf10_b10_40NTDview on byf10_b10_30b10_40view.b10_30b10_40B10_01=byf10_b10_40NTDview.b10_40NTDB10_01 GOdrop view byf10_b10_30NTDviewdrop view byf10_b10_40NTDviewdrop view byf10_b10_30b10_40viewGOselect a.b10_30b10_40B10_01,a.B10_09,a.B10_17,a.B10_30,a.B10_31,a.B10_40,a.B10_43,a.B10_46,a.b10_30b10_40B01_05,b.b10_30NTDB01_04, c.b10_40NTDB01_04from(select DISTINCT byf10.B10_01 as b10_30b10_40B10_01,byf01.B01_05 as b10_30b10_40B01_05,byf10.B10_09,byf10.B10_17,byf10.B10_30,byf10.B10_31,byf10.B10_40,byf10.B10_43,byf10.B10_46from byf10 left join byf01 on byf10.B10_30+byf10.B10_40=byf01.B01_02+byf01.B01_03 where byf01.B01_01<=byf10.b10_02 and byf10.b10_02<=byf01.B01_011)as aleft join(select DISTINCT byf10.B10_01 as b10_30NTDB10_01,byf01.B01_04 as b10_30NTDB01_04from byf10 left join byf01 on byf10.B10_30+'NTD'=byf01.B01_02+byf01.B01_03 where byf01.B01_01<=byf10.b10_02 and byf10.b10_02<=byf01.B01_011) as b on a.b10_30b10_40B10_01=b.b10_30NTDB10_01 left join (select DISTINCT byf10.B10_01 as b10_40NTDB10_01,byf01.B01_04 as b10_40NTDB01_04from byf10 left join byf01 on byf10.B10_40+'NTD'=byf01.B01_02+byf01.B01_03 where byf01.B01_01<=byf10.b10_02 and byf10.b10_02<=byf01.B01_011)as c on a.b10_30b10_40B10_01=c.b10_40NTDB10_01 GOCREATE TABLE [byf01] ( [B01_01] [datetime] NULL , [B01_011] [datetime] NULL , [B01_02] [nvarchar] (3) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL , [B01_03] [nvarchar] (3) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL , [B01_04] [decimal](6, 4) NULL , [bName] [char] (10) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL , [B01_06] [bit] NULL , [bdate] [datetime] NULL , [B01_07] [decimal](18, 4) NULL , [B01_08] [decimal](18, 4) NULL , [ID] [int] IDENTITY (1, 1) NOT NULL , [B01_Span] [decimal](6, 4) NULL , [B01_05] [decimal](6, 4) NULL ) ON [PRIMARY]GOCREATE TABLE [byf10] ( [ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL , [B10_01] [nvarchar] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL , [B10_011] [nvarchar] (10) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL , [B10_02] [datetime] NULL , [B10_03] [nvarchar] (1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL , [B10_04] [nvarchar] (7) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL , [B10_05] [nvarchar] (40) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL , [B10_06] [nvarchar] (25) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL , [B10_07] [nvarchar] (16) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL , [B10_08] [nvarchar] (30) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL , [B10_09] [nvarchar] (4) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL , [B10_10] [nvarchar] (2) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL , [B10_11] [bit] NULL , [B10_12] [nvarchar] (7) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL , [B10_13] [nvarchar] (40) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL , [B10_14] [nvarchar] (25) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL , [B10_15] [nvarchar] (16) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL , [B10_16] [nvarchar] (30) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL , [B10_17] [nvarchar] (4) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL , [B10_18] [nvarchar] (2) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL , [B10_19] [bit] NULL , [B10_21] [numeric](8, 2) NULL , [B10_22] [numeric](8, 4) NULL , [B10_23] [numeric](8, 4) NULL , [B10_30] [nvarchar] (3) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL , [B10_31] [numeric](8, 2) NULL , [B10_32] [nvarchar] (1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL , [B10_33] [decimal](8, 2) NULL , [B10_34] [decimal](8, 2) NULL , [B10_35] [decimal](8, 2) NULL , [B10_36] [decimal](8, 2) NULL , [B10_37] [decimal](8, 2) NULL , [B10_40] [nvarchar] (3) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL , [B10_41] [decimal](8, 0) NULL , [B10_42] [nvarchar] (1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL , [B10_43] [decimal](8, 2) NULL , [B10_45] [decimal](8, 2) NULL , [B10_46] [decimal](8, 2) NULL , [B10_47] [decimal](8, 2) NULL , [del] [bit] NULL , [comID] [nvarchar] (4) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL , [bName] [nvarchar] (10) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL , [bdate] [datetime] NULL , [B10_48] [decimal](6, 4) NULL , [B10_49] [decimal](6, 4) NULL , [B10_50] [decimal](6, 4) NULL ) ON [PRIMARY]GO
