作者kofi0916 (Nick)
看板Database
標題Re: [SQL ] MS SQL PIVOT語法
時間Tue Apr 10 15:23:40 2012
我的做法如下, 請參考
DECLARE @Temp TABLE ([FeederName] nvarchar(50) , [Longitude] decimal(7,4) ,
[Latitude] decimal(7,4),[ORDER] NVARCHAR(10),[FeederCode_NEW] NVARCHAR(20))
INSERT INTO @Temp (
[FeederName],[Longitude],[Latitude],[ORDER],[FeederCode_NEW] )
select [FeederName],[Longitude],[Latitude] ,[ORDER],[FeederCode_NEW]
from [dbo].[TaipowerAccount]
DECLARE @Temp1 TABLE ( [Longitude] decimal(7,4) , [Latitude] decimal(7,4),
[FeederName1] nvarchar(50) ,[ORDER1] NVARCHAR(10),[FeederCode1_NEW]
NVARCHAR(20),
[FeederName2] nvarchar(50) ,[ORDER2] NVARCHAR(10),[FeederCode2_NEW]
NVARCHAR(20),
[FeederName3] nvarchar(50) ,[ORDER3] NVARCHAR(10),[FeederCode3_NEW]
NVARCHAR(20),
[FeederName4] nvarchar(50) ,[ORDER4] NVARCHAR(10),[FeederCode4_NEW]
NVARCHAR(20),
[FeederName5] nvarchar(50) ,[ORDER5] NVARCHAR(10),[FeederCode5_NEW]
NVARCHAR(20),
[FeederName6] nvarchar(50) ,[ORDER6] NVARCHAR(10),[FeederCode6_NEW]
NVARCHAR(20))
INSERT INTO @Temp1 ( [Longitude],[Latitude] )
select [Longitude],[Latitude]
from [dbo].[TaipowerAccount]
group by [Longitude],[Latitude]
declare @Longi decimal(7,4), @Lati decimal(7,4),@FeederName nvarchar(50) ,
@Longitude decimal(7,4) ,
@Latitude decimal(7,4),@ORDER NVARCHAR(10),@FeederCode_NEW NVARCHAR(20)
DECLARE mycursor Cursor FOR
select [Longitude],[Latitude] from @Temp1 order by [Longitude],[Latitude]
OPEN mycursor
DECLARE mycursor1 Cursor FOR
select [Longitude],[Latitude],[FeederName],[ORDER],[FeederCode_NEW] from
@Temp order by [Longitude],[Latitude]
OPEN mycursor1
FETCH NEXT FROM mycursor1
INTO @Longitude, @Latitude,@FeederName,@ORDER,@FeederCode_NEW
FETCH NEXT FROM mycursor
INTO @Longi,@Lati
WHILE @@FETCH_STATUS = 0
BEGIN
if @@FETCH_STATUS = 0 and @Longi = @Longitude and @Lati = @Latitude
begin
update @Temp1 set [FeederName1]=@FeederName, [ORDER1] = @ORDER,
[FeederCode1_NEW] = @FeederCode_NEW
where [Longitude]=@Longi and [Latitude]= @Lati
FETCH NEXT FROM mycursor1
INTO @Longitude, @Latitude,@FeederName,@ORDER,@FeederCode_NEW
if @@FETCH_STATUS = 0 and @Longi = @Longitude and @Lati = @Latitude
begin
update @Temp1 set [FeederName2]=@FeederName, [ORDER2] = @ORDER,
[FeederCode2_NEW] = @FeederCode_NEW
where [Longitude]=@Longi and [Latitude]= @Lati
FETCH NEXT FROM mycursor1
INTO @Longitude, @Latitude,@FeederName,@ORDER,@FeederCode_NEW
if @@FETCH_STATUS = 0 and @Longi = @Longitude and @Lati = @Latitude
begin
update @Temp1 set [FeederName3]=@FeederName, [ORDER3] = @ORDER,
[FeederCode3_NEW] = @FeederCode_NEW
where [Longitude]=@Longi and [Latitude]= @Lati
FETCH NEXT FROM mycursor1
INTO @Longitude, @Latitude,@FeederName,@ORDER,@FeederCode_NEW
if @@FETCH_STATUS = 0 and @Longi = @Longitude and @Lati = @Latitude
begin
update @Temp1 set [FeederName4]=@FeederName, [ORDER4] = @ORDER,
[FeederCode4_NEW] = @FeederCode_NEW
where [Longitude]=@Longi and [Latitude]= @Lati
FETCH NEXT FROM mycursor1
INTO @Longitude, @Latitude,@FeederName,@ORDER,@FeederCode_NEW
if @@FETCH_STATUS = 0 and @Longi = @Longitude and @Lati = @Latitude
begin
update @Temp1 set [FeederName5]=@FeederName, [ORDER5] = @ORDER,
[FeederCode5_NEW] = @FeederCode_NEW
where [Longitude]=@Longi and [Latitude]= @Lati
FETCH NEXT FROM mycursor1
INTO @Longitude, @Latitude,@FeederName,@ORDER,@FeederCode_NEW
if @@FETCH_STATUS = 0 and @Longi = @Longitude and @Lati = @Latitude
begin
update @Temp1 set [FeederName6]=@FeederName, [ORDER6] = @ORDER,
[FeederCode6_NEW] = @FeederCode_NEW
where [Longitude]=@Longi and [Latitude]= @Lati
end
end
end
end
end
end
WHILE @@FETCH_STATUS = 0 and @Longi = @Longitude and @Lati = @Latitude
BEGIN
FETCH NEXT FROM mycursor1
INTO @Longitude, @Latitude,@FeederName,@ORDER,@FeederCode_NEW
END
FETCH NEXT FROM mycursor
INTO @Longi,@Lati
END
CLOSE mycursor
DEALLOCATE mycursor
CLOSE mycursor1
DEALLOCATE mycursor1
select * from @Temp1
GO
※ 引述《f127doggpig (阿賢)》之銘言：
: 各位好 目前我用DECLARE在＠TEMP增加欄位
: DECLARE @Temp TABLE ([FeederName] nvarchar(50) , [Longitude] decimal(7,4) ,
: [Latitude] decimal(7,4),[ORDER] NVARCHAR(10),[FeederCode_NEW] NVARCHAR(20))
: INSERT INTO @Temp ( [FeederName],[Longitude],[Latitude],[ORDER],[FeederCode_NEW] )select [FeederName],[Longitude],[Latitude] ,[ORDER],[FeederCode_NEW]
: from [Tlis].[dbo].[TaipowerAccount]
: 每一筆的資料
: [FeederName]可能是一樣[FeederCode_NEW]也會是一樣，但是藉由[Longitude],
: [Latitude],[ORDER]來區分
: 也就是
: [FeederName],[Longitude],[Latitude],[ORDER],[FeederCode_NEW]
: A , 120 , 50 , 1 , WS
: A , 121 , 12 , 5 , WS
: B , 120 , 50 , 3 , SD
: B , 52 , 12 , 4 , SD
: C , 120 , 50 , 1 , WE
: C , 52 , 12 , 3 , WE
: Z
: PIVOT 之後
: 變成以相同的[Longitude],[Latitude]列出重複的前6筆資料分別有他們的[FeederName],
: [ORDER],[FeederCode_NEW]
: 也就是
: 120,50,A,1 ,WS,B,3,SD,C,1,WE,NULL,NULL,NULL,NULL,NULL....... 補滿第6筆
: 121,12,A,12,WS,,NULL,NULL,NULL,NULL,........................ 補滿第6筆
: 我該如何pivot呢？ 謝謝
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 211.22.167.211