![VFP 愛用者社區 首頁](templates/subSilver/images/vfps.jpg) |
VFP 愛用者社區 本討論區為 Visual Foxpro 愛用者經驗交流的地方, 請多多利用"搜尋"的功能, 先查看看有無前例可循, 如果還有不懂的再發問. 部份主題有附加檔案, 須先註冊成為社區居民才可以下載.
|
上一篇主題 :: 下一篇主題 |
發表人 |
內容 |
sthuang66
註冊時間: 2014-04-27 文章: 173
第 1 樓
|
發表於: 星期三 一月 27, 2016 1:29 am 文章主題: 不定列行列轉換(直轉橫) |
|
|
資料格式如下
name area
john 內湖區
john 中山區
john 萬華區
tom 貢寮區
tom 瑞芳區
mary 內湖區
mary 中山區
mary 板橋區
mary 中和區
bob 永和區
轉成橫式
name area1 area2 area3 area4
john 內湖區 中山區 萬華區
tom 貢寮區 瑞芳區
mary 內湖區 中山區 板橋區 中和區
bob 永和區
--------------------------------------------------------------
之前有在論壇請教過屬於可數性的轉換( 直轉橫 ) 用IIF與SUM就可以!!!
這次遇到是不定的就考倒我了!有GOOGLE 一下如下
------------------------------------------------------------------
CREATE OR REPLACE FUNCTION get_c2(tmp_c1 NUMBER)
RETURN VARCHAR2
IS
Col_c2 VARCHAR2(4000);
BEGIN
FOR cur IN (SELECT c2 FROM t WHERE c1=tmp_c1) LOOP
Col_c2 := Col_c2||cur.c2;
END LOOP;
Col_c2 := rtrim(Col_c2,1);
RETURN Col_c2;
END;
/
SQL> select distinct c1 ,get_c2(c1) cc2 from table;即可
-------------------------------------------------------------------------
看起來不是VFP語法!所以懇請各位高手幫忙是否可以改成VFP語法!感恩 |
|
回頂端 |
|
![](templates/subSilver/images/spacer.gif) |
perry
註冊時間: 2014-07-20 文章: 203
第 2 樓
|
發表於: 星期三 一月 27, 2016 12:34 pm 文章主題: |
|
|
先試試下面這個土法煉鋼方式!!
代碼: |
proc testaaa
sele 0
crea curs areadata (name c(20),area c(6))
inse into areadata valu ('john','內湖區')
inse into areadata valu ('john','中山區')
inse into areadata valu ('john','萬華區')
inse into areadata valu ('tom','貢寮區')
inse into areadata valu ('tom','瑞芳區')
inse into areadata valu ('mary','內湖區')
inse into areadata valu ('mary','中山區')
inse into areadata valu ('mary','板橋區')
inse into areadata valu ('mary','中和區')
inse into areadata valu ('bob','永和區')
sele 0
sele name,area from areadata grou by name,area orde by name,area into curs test1
go top
priv i,j,j1,x_name,x_area,x_namel,x_areal
i=0
j=0
x_area=''
x_namel=len(name)
x_name=spac(x_namel)
x_areal=len(area)
scan
if name#x_name
x_name=name
j=0
x_area=''
endi
if at(area+chr(13),x_area)<1
x_area=x_area+area+chr(13)
j=j+1
if j>i
i=j
endi
endi
ends
crea_str='name c('+allt(str(x_namel))+')'
for j=1 to i
j1='area'+allt(str(j))+' c('+allt(str(x_areal))+')'
crea_str=crea_str+','+j1
next
sele 0
crea curs test2 (&crea_str)
sele test1
go top
x_name=spac(x_namel)
scan
sele test2
if test1.name#x_name
x_name=test1.name
inse into test2 (name) valu (test1.name)
j=1
else
j=j+1
endi
j1='area'+allt(str(j))
repl &j1 with test1.area
sele test1
ends
sele test2
go top
brow
use in test2
use in test1
use in areadata
|
|
|
回頂端 |
|
![](templates/subSilver/images/spacer.gif) |
sthuang66
註冊時間: 2014-04-27 文章: 173
第 3 樓
|
發表於: 星期三 一月 27, 2016 6:21 pm 文章主題: |
|
|
非常感謝指導!我試試 |
|
回頂端 |
|
![](templates/subSilver/images/spacer.gif) |
perry
註冊時間: 2014-07-20 文章: 203
第 4 樓
|
發表於: 星期四 一月 28, 2016 8:12 am 文章主題: |
|
|
用 alter table 方式可一次 scan 就完成!!
crea dbf test2 (name c(20))
.
.
.
sele test2
j1='area'+allt(str(j))
if j>i
i=j
alter table test2 add column &j1 c(6)
go bott
endi
repl &j1 with test1.area |
|
回頂端 |
|
![](templates/subSilver/images/spacer.gif) |
sthuang66
註冊時間: 2014-04-27 文章: 173
第 5 樓
|
發表於: 星期四 一月 28, 2016 10:07 am 文章主題: |
|
|
請教Perry兄:
您第一次寫的程式很仔細!我功力較弱正在試著看懂他!
第二次所寫的方法很精簡!只是第一次你的方法我沒有弄得很懂!所以 那三個...我不是很清楚要取代第一次的哪裡?真的很抱歉又來麻煩你!
-----------------------------------------------------------------------------
我有很認真GOOGLE關於用VFP來做轉換的答案結果只有中國網友的邏輯如下
*********************************
思路 excel 或 vfp :
1.从左表第一行开始,逐行扫描。(excel 用 for,vfp 用 SCAN)
2.分别用变量暂存当前行 ksh,xm,kcmc, dj 字段的值
4.通过当前 ksh,扫描右表有没对应的值,
如果有,通过当前 kcmc ,excel 把当前 dj 写入相应单元格,VFP 用 REPL
如果没有,excel 在新行相应单元格写入当前 ksh,xm,kcmc, dj,VFP 用 INSERT INTO
*****************************************
我沒有把他的表貼出來,不過他想做的事跟我一樣!看你的步驟跟以上邏輯我大概快要弄懂了!
------------------------------------------------------------------------------
只是網上查的到的都是說要用動態SQL,不然就是要用pivot函數( MSSQL)
真得很感謝你的熱心!讓我又多學了一點 |
|
回頂端 |
|
![](templates/subSilver/images/spacer.gif) |
perry
註冊時間: 2014-07-20 文章: 203
第 6 樓
|
發表於: 星期四 一月 28, 2016 10:19 am 文章主題: |
|
|
代碼: |
proc testaaa
priv i,j,j1,x_name
sele 0
crea curs areadata (name c(20),area c(6))
inse into areadata valu ('john','內湖區')
inse into areadata valu ('john','中山區')
inse into areadata valu ('john','萬華區')
inse into areadata valu ('tom','貢寮區')
inse into areadata valu ('tom','瑞芳區')
inse into areadata valu ('mary','內湖區')
inse into areadata valu ('mary','中山區')
inse into areadata valu ('mary','板橋區')
inse into areadata valu ('mary','中和區')
inse into areadata valu ('bob','永和區')
sele 0
sele name,area from areadata grou by name,area orde by name,area into curs test1
sele 0
crea dbf test2 (name c(20))
sele test1
go top
store 0 to i,j
j1=''
x_name=spac(20)
scan
sele test2
if test1.name#x_name &&當姓名欄位和變數不同新增一筆記錄
x_name=test1.name
inse into test2 (name) valu (test1.name)
j=0
endi
j=j+1
j1='area'+allt(str(j))
if j>i &&新增 area 欄位
i=j
alter table test2 add column &j1 c(6)
go bott &&新增欄位時記錄會在第1筆,所以要移到最後一筆
endi
repl &j1 with test1.area
sele test1
ends
sele test2
go top
brow
use in test1
use in areadata
use in test2
eras test2.dbf &&刪除暫存檔
|
透過傳值方式,例如
=testaaa(資料庫名稱或別名,主欄位,型態長度,動態欄位,型態長度)
=testaaa('areadata','name','c(20)','area','c(6)')
可寫出共用的函數,雖然可用 copy stru or afield() 來讀取欄位型度及長度.
proc testaaa && or testaaa.prg
lpar c_alia,c_fd,c_type,c_fd1,c_type1
priv ss_str
ss_str=c_fd+','+c_fd1
crea_str1=c_fd+' '+c_type
sele 0
crea dbf test2 (&crea_str1)
sele 0
sele &ss_str from (c_alia) grou by &ss_str orde by &ss_str into curs test1
.
.
但個人覺得不確定的欄位直轉橫,應該不會常常用到. |
|
回頂端 |
|
![](templates/subSilver/images/spacer.gif) |
sthuang66
註冊時間: 2014-04-27 文章: 173
第 7 樓
|
發表於: 星期四 一月 28, 2016 1:14 pm 文章主題: |
|
|
感謝Perry大力相助!我的案子大概只有一兩百筆資料直轉橫!的確是不會太常使用!就算用!也是新增的橫向的欄位也不會超過10個!通常少時五個
雖然利用EXCEL手動操作也可以!只是資料多一點時會按到手痠一點!所以才會想尋求程式解決!
網路上解決的方法都是oracle或是MSSQL的程式!我都不熟悉!
您新方法很精簡!試過也可以了!十分感謝! |
|
回頂端 |
|
![](templates/subSilver/images/spacer.gif) |
sthuang66
註冊時間: 2014-04-27 文章: 173
第 8 樓
|
發表於: 星期四 二月 25, 2016 9:18 pm 文章主題: |
|
|
可以再請教一個問題嗎?
表B
name area1 area2 area3 area4
john 內湖區 中山區 萬華區
tom 貢寮區 瑞芳區
mary 內湖區 中山區 板橋區 中和區
bob 永和區
當把不定直轉橫後如上! 因為產生的Area"N"是不一定 所以當我要把它和另外的表A link 起來時有個問題
-------------------------------------------------------------
表A
Name Sale_no
john 10
tom 5
mary 9
bob 6
----------------------------------------------------------------------------
select A.name,A.sale_no,B.area1,B.area2,B.area3,B.area4 from A inner join B on A.name=B.Name into table C
----------------------------------------------------------------------------
可能每個月產生的AREA"N"會不同!那麼要link時不是要準備好幾個!!
原諒我這個菜鳥發問 |
|
回頂端 |
|
![](templates/subSilver/images/spacer.gif) |
perry
註冊時間: 2014-07-20 文章: 203
第 9 樓
|
發表於: 星期四 二月 25, 2016 10:23 pm 文章主題: |
|
|
有年月需求,何不在原始資料表A增加年月的欄位,事情就簡單多了@@
欄位 name,area,yemo
*d1=起始年月
*d2=截止年月
sele * from a wher yemo>=d1 and yemo<=d2 |
|
回頂端 |
|
![](templates/subSilver/images/spacer.gif) |
sthuang66
註冊時間: 2014-04-27 文章: 173
第 10 樓
|
發表於: 星期五 二月 26, 2016 2:31 am 文章主題: |
|
|
我沒有表達清楚我的問題!
我的意思是我有每個月會需要不定直轉橫的區域A.DBF!但是轉完後要把該月總業績B.DBF join
但是因為是不定直轉橫!有可能轉完這個月AREA有1~4 area1,area2,area3,area4
但是下個月可能轉完較少 只有AREA1,AREA2,AREA3
所以我join 的SQL程序就得視直轉橫後才能人工判斷
是4個 就是
select A.name,A.sale_no,B.area1,B.area2,B.area3,B.area4 from A inner join B on A.name=B.Name into table C
三個就
select A.name,A.sale_no,B.area1,B.area2,B.area3 from A inner join B on A.name=B.Name into table D
----------------------------------------------------------
有其它辦法嗎?我有想union好像不行 update也不是
還是? |
|
回頂端 |
|
![](templates/subSilver/images/spacer.gif) |
perry
註冊時間: 2014-07-20 文章: 203
第 11 樓
|
發表於: 星期五 二月 26, 2016 3:43 am 文章主題: |
|
|
簡單的動態字串相加就可達成!!
代碼: |
join_str='A.name,A.sale_no'
scan
.
.
if j>i &&新增 area 欄位
i=j && 這是最大欄位數
*產生SQL自動對應欄位數字串
join_str=join_str+',B.area'+allt(str(j))
alter table test2 add column &j1 c(6)
go bott &&新增欄位時記錄會在第1筆,所以要移到最後一筆
endi
.
.
ends
sele 0
sele &join_str from A inner join B on A.name=B.Name into table C
|
sele b.*,a.sale_no from a inner join b on a.name=b.name into table c
sele b.*,a.sale_no 方式不適合用在 Left Join or Right Join |
|
回頂端 |
|
![](templates/subSilver/images/spacer.gif) |
sthuang66
註冊時間: 2014-04-27 文章: 173
第 12 樓
|
發表於: 星期六 二月 27, 2016 4:53 pm 文章主題: |
|
|
太感謝指導 |
|
回頂端 |
|
![](templates/subSilver/images/spacer.gif) |
sthuang66
註冊時間: 2014-04-27 文章: 173
第 13 樓
|
發表於: 星期一 十月 30, 2017 1:05 am 文章主題: 新的需求 |
|
|
資料格式如下
name area SCORE
john 內湖區 2
john 中山區 3
john 萬華區 6
tom 貢寮區 4
tom 瑞芳區 4
mary 內湖區 2
mary 中山區 1
mary 板橋區 6
mary 中和區 9
bob 永和區 10
轉成橫式
name area1,AREA1_SCORE, area2,AREA2_SCORE,area3,AREA3_SCORE, area4,AREA4_SCORE
john 內湖區 2 中山區 3 萬華區 4
tom 貢寮區 4 瑞芳區 4
mary 內湖區 2 中山區 1 板橋區 6 中和區 9
bob 永和區 10
--------------------------------------------------------------------------
PERRY兄說 sele b.*,a.SCORE 方式不適合用在 Left Join or Right Join
所以我就土法煉鋼用DO CASE 方法!是可以算出來!雖然我的AREA數目是不會到超過十個!但是要寫十個DO CASE很煩又長
我是暫時寫了四個來用!想請教是否有更簡便的方法!謝謝各位高手不吝教導!!!!
-------------------------------------------------------------------------
priv i,j,j1,x_name
sele 0
crea TABLE areadata (name c(20),area c(6),SCORE N(4))
inse into areadata valu ('john','內湖區',2)
inse into areadata valu ('john','中山區',3)
inse into areadata valu ('john','萬華區',6)
inse into areadata valu ('tom','貢寮區',4)
inse into areadata valu ('tom','瑞芳區',4)
inse into areadata valu ('mary','內湖區',2)
inse into areadata valu ('mary','中山區',1)
inse into areadata valu ('mary','板橋區',6)
inse into areadata valu ('mary','中和區',9)
inse into areadata valu ('bob','永和區',10)
sele 0
sele name,area from areadata grou by name,area orde by name,area into curs test1
sele 0
crea dbf test2 (name c(20))
sele test1
go top
store 0 to i,j
j1=''
x_name=spac(20)
scan
sele test2
if test1.name#x_name &&當姓名欄位和變數不同新增一筆記錄
x_name=test1.name
inse into test2 (name) valu (test1.name)
j=0
endi
j=j+1
j1='area'+allt(str(j))
if j>i &&新增 area 欄位
i=j
alter table test2 add column &j1 c(6)
go bott &&新增欄位時記錄會在第1筆,所以要移到最後一筆
endi
repl &j1 with test1.area
sele test1
ends
sele test2
go top
COPY TO H12
use in test1
use in areadata
use in test2
eras test2.dbf &&刪除暫存檔
CLOSE DATABASES ALL
USE H12
A=FCOUNT()-1
DO CASE
CASE A=2
SELECT H12.*, AREADATA.SCORE AS AREA1_S FROM H12 LEFT JOIN AREADATA ON H12.NAME = AREADATA.NAME AND H12.AREA1 = AREADATA.AREA INTO CURSOR A1
SELECT A1.*,AREADATA.SCORE AS AREA2_S FROM A1 LEFT JOIN AREADATA ON A1.NAME = AREADATA.NAME AND A1.AREA2 = AREADATA.AREA INTO CURSOR A2
SELECT NAME,AREA1,AREA1_S,AREA2,AREA2_S FROM A2 INTO CURSOR TEMP
COPY TO NN TYPE XL5
CASE A=3
SELECT H12.*, AREADATA.SCORE AS AREA1_S FROM H12 LEFT JOIN AREADATA ON H12.NAME = AREADATA.NAME AND H12.AREA1 = AREADATA.AREA INTO CURSOR A1
SELECT A1.*,AREADATA.SCORE AS AREA2_S FROM A1 LEFT JOIN AREADATA ON A1.NAME = AREADATA.NAME AND A1.AREA2 = AREADATA.AREA INTO CURSOR A2
SELECT A2.*,AREADATA.SCORE AS AREA3_S FROM A2 LEFT JOIN AREADATA ON A2.NAME = AREADATA.NAME AND A2.AREA3= AREADATA.AREA INTO CURSOR A3
SELECT NAME,AREA1,AREA1_S,AREA2,AREA2_S,AREA3,AREA3_S FROM A3 INTO CURSOR TEMP
COPY TO NN TYPE XL5
CASE A=4
SELECT H12.*, AREADATA.SCORE AS AREA1_S FROM H12 LEFT JOIN AREADATA ON H12.NAME = AREADATA.NAME AND H12.AREA1 = AREADATA.AREA INTO CURSOR A1
SELECT A1.*,AREADATA.SCORE AS AREA2_S FROM A1 LEFT JOIN AREADATA ON A1.NAME = AREADATA.NAME AND A1.AREA2 = AREADATA.AREA INTO CURSOR A2
SELECT A2.*,AREADATA.SCORE AS AREA3_S FROM A2 LEFT JOIN AREADATA ON A2.NAME = AREADATA.NAME AND A2.AREA3= AREADATA.AREA INTO CURSOR A3
SELECT A3.*,AREADATA.SCORE AS AREA4_S FROM A3 LEFT JOIN AREADATA ON A3.NAME = AREADATA.NAME AND A3.AREA4 =AREADATA.AREA INTO CURSOR A4
SELECT NAME,AREA1,AREA1_S,AREA2,AREA2_S,AREA3,AREA3_S,AREA4,AREA4_S FROM A4 INTO CURSOR TEMP
COPY TO NN TYPE XL5
ENDCASE |
|
回頂端 |
|
![](templates/subSilver/images/spacer.gif) |
perry
註冊時間: 2014-07-20 文章: 203
第 14 樓
|
發表於: 星期一 十月 30, 2017 6:45 am 文章主題: |
|
|
若是要產生XLS,不用 copy to ,
直接在 EXCEL 上動作即可
代碼: |
PRIV XLApp,XLSheet,x_name,i,j,k
XLApp=crea('excel.application')
XLApp.WorkBooks.add
XLSheet=XLApp.WorkBooks(1).Sheets(1)
XLSheet.Cells(1,1)="'名字"
x_name=spac(20)
sele test1
go top
store 0 to i,j && i 欄計數變數,j 所屬欄位變數
k=1 &&列計數變數
scan
if name#x_name
x_name=name
j=0
k=k+1
XLSheet.Cells(k,1).Value="'"+allt(name)
endi
j=j+1
if j>i
i=j
XLSheet.Cells(1,i+1).Value="'"+'地區' &&增�[欄
*XLSheet.Columns(i+1).Width=30 &&修改欄位寛度
endi
XLSheet.Cells(k,j+1).Value="'"+allt(area)
ends
XLApp.Visible=.T.
XLSheet.PrintPreview
XLApp.WorkBooks(1).Saved=.T. &&不存檔
XLApp.WorkBooks(1).Close
XLApp.QUIT
rele XLApp,XLSheet
|
可先設計好空白報表試算表直接在Execl上填資料,
作業上會快很多!! |
|
回頂端 |
|
![](templates/subSilver/images/spacer.gif) |
ckp6250
註冊時間: 2004-07-30 文章: 1644
第 15 樓
|
發表於: 星期一 十月 30, 2017 9:37 am 文章主題: |
|
|
很可惜,如果是 mysql 的話,這事就不費吹灰之力了。
一個函數搞定。
代碼: |
SELECT
areadata.NAME,
group_concat(areadata.AREA) as area
FROM
areadata
GROUP BY
areadata.NAME
|
|
|
回頂端 |
|
![](templates/subSilver/images/spacer.gif) |
|
|
您 無法 在這個版面發表文章 您 無法 在這個版面回覆文章 您 無法 在這個版面編輯文章 您 無法 在這個版面刪除文章 您 無法 在這個版面進行投票 您 無法 在這個版面附加檔案 您 無法 在這個版面下載檔案
|
|