【SQL】行列轉換方法示例
一、行變列
1.建立測試表test,並初始化實驗資料
sec@ora10g> create table test (name_id varchar2(10), name varchar2(10));
sec@ora10g> insert into test values ('01','Andy1');
sec@ora10g> insert into test values ('01','Andy2');
sec@ora10g> insert into test values ('01','Andy3');
sec@ora10g> insert into test values ('01','Andy4');
sec@ora10g> insert into test values ('02','Steven1');
sec@ora10g> insert into test values ('02','Steven2');
sec@ora10g> insert into test values ('02','Steven3');
sec@ora10g> insert into test values ('02','Steven4');
sec@ora10g> commit;
2.檢視測試表資料
sec@ora10g> select * from test order by name_id;
NAME_ID NAME
---------- ----------------------------------------
01 Andy1
01 Andy4
01 Andy3
01 Andy2
02 Steven1
02 Steven4
02 Steven3
02 Steven2
8 rows selected.
3.我們按照name_id不同值將該表橫向轉換如下
SELECT t1.name_id,
SUBSTR (MAX (SYS_CONNECT_BY_PATH (t1.NAME, ';')), 2) NAME
FROM (SELECT a.name_id, a.NAME,
ROW_NUMBER () OVER (PARTITION BY a.name_id ORDER BY a.NAME) rn
FROM TEST a) t1
START WITH t1.rn = 1
CONNECT BY t1.name_id = PRIOR t1.name_id AND t1.rn - 1 = PRIOR t1.rn
GROUP BY t1.name_id;
NAME_ID NAME
---------- ----------------------------------------
01 Andy1;Andy2;Andy3;Andy4
02 Steven1;Steven2;Steven3;Steven4
該條SQL語句的編寫思想是:
1)根據分組後的行號,構造一棵樹(或者多棵)。
2)把從根到葉子的值串接起來。
二、列變行
1.重新構造測試表test,並初始化實驗資料
sec@ora10g> drop table test purge;
sec@ora10g> create table test (name_id varchar2(10), name varchar2(40));
sec@ora10g> insert into test values ('01','Andy1;Andy2;Andy3;Andy4');
sec@ora10g> insert into test values ('02','Steven1;Steven2;Steven3;Steven4');
sec@ora10g> commit;
2.檢視測試表資料
sec@ora10g> select * from test;
NAME_ID NAME
---------- ----------------------------------------
01 Andy1;Andy2;Andy3;Andy4
02 Steven1;Steven2;Steven3;Steven4
3.行變列轉換的SQL語句如下
SELECT t.name_id,
SUBSTR (t.name,
INSTR (';' || t.name, ';', 1, rn),
INSTR (t.name || ';', ';', 1, rn)
- INSTR (';' || t.name, ';', 1, rn)
) name
FROM (SELECT a.name_id, a.name, b.rn
FROM test a,
(SELECT ROWNUM rn
FROM DUAL
CONNECT BY ROWNUM <= 100) b
WHERE INSTR (';' || a.name, ';', 1, rn) > 0) t
ORDER BY 1, 2;
NAME_ID NAME
---------- ----------------------------------------
01 Andy1
01 Andy2
01 Andy3
01 Andy4
02 Steven1
02 Steven2
02 Steven3
02 Steven4
8 rows selected.
該條SQL語句的編寫思想
1)構造虛擬表把源資料的行數增加
2)根據行號選擇顯示欄位的不同部分
secooler
10.09.27
-- The End --
1.建立測試表test,並初始化實驗資料
sec@ora10g> create table test (name_id varchar2(10), name varchar2(10));
sec@ora10g> insert into test values ('01','Andy1');
sec@ora10g> insert into test values ('01','Andy2');
sec@ora10g> insert into test values ('01','Andy3');
sec@ora10g> insert into test values ('01','Andy4');
sec@ora10g> insert into test values ('02','Steven1');
sec@ora10g> insert into test values ('02','Steven2');
sec@ora10g> insert into test values ('02','Steven3');
sec@ora10g> insert into test values ('02','Steven4');
sec@ora10g> commit;
2.檢視測試表資料
sec@ora10g> select * from test order by name_id;
NAME_ID NAME
---------- ----------------------------------------
01 Andy1
01 Andy4
01 Andy3
01 Andy2
02 Steven1
02 Steven4
02 Steven3
02 Steven2
8 rows selected.
3.我們按照name_id不同值將該表橫向轉換如下
SELECT t1.name_id,
SUBSTR (MAX (SYS_CONNECT_BY_PATH (t1.NAME, ';')), 2) NAME
FROM (SELECT a.name_id, a.NAME,
ROW_NUMBER () OVER (PARTITION BY a.name_id ORDER BY a.NAME) rn
FROM TEST a) t1
START WITH t1.rn = 1
CONNECT BY t1.name_id = PRIOR t1.name_id AND t1.rn - 1 = PRIOR t1.rn
GROUP BY t1.name_id;
NAME_ID NAME
---------- ----------------------------------------
01 Andy1;Andy2;Andy3;Andy4
02 Steven1;Steven2;Steven3;Steven4
該條SQL語句的編寫思想是:
1)根據分組後的行號,構造一棵樹(或者多棵)。
2)把從根到葉子的值串接起來。
二、列變行
1.重新構造測試表test,並初始化實驗資料
sec@ora10g> drop table test purge;
sec@ora10g> create table test (name_id varchar2(10), name varchar2(40));
sec@ora10g> insert into test values ('01','Andy1;Andy2;Andy3;Andy4');
sec@ora10g> insert into test values ('02','Steven1;Steven2;Steven3;Steven4');
sec@ora10g> commit;
2.檢視測試表資料
sec@ora10g> select * from test;
NAME_ID NAME
---------- ----------------------------------------
01 Andy1;Andy2;Andy3;Andy4
02 Steven1;Steven2;Steven3;Steven4
3.行變列轉換的SQL語句如下
SELECT t.name_id,
SUBSTR (t.name,
INSTR (';' || t.name, ';', 1, rn),
INSTR (t.name || ';', ';', 1, rn)
- INSTR (';' || t.name, ';', 1, rn)
) name
FROM (SELECT a.name_id, a.name, b.rn
FROM test a,
(SELECT ROWNUM rn
FROM DUAL
CONNECT BY ROWNUM <= 100) b
WHERE INSTR (';' || a.name, ';', 1, rn) > 0) t
ORDER BY 1, 2;
NAME_ID NAME
---------- ----------------------------------------
01 Andy1
01 Andy2
01 Andy3
01 Andy4
02 Steven1
02 Steven2
02 Steven3
02 Steven4
8 rows selected.
該條SQL語句的編寫思想
1)構造虛擬表把源資料的行數增加
2)根據行號選擇顯示欄位的不同部分
secooler
10.09.27
-- The End --
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/519536/viewspace-609167/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 行列轉換sqlSQL
- sql server 行列轉換SQLServer
- sql server行列轉換案例SQLServer
- 【SQL 學習】行列轉換SQL
- 通用的行列轉換的方法
- sql 實現表的行列轉換SQL
- Oracle--SQL行列轉換實戰OracleSQL
- SQL Server中行列轉換 Pivot UnPivotSQLServer
- 行列轉換
- sql行列轉置的實現方法SQL
- SQL Server 實現行列(縱橫表)轉換SQLServer
- Kettle行列轉換
- 偽行列轉換!
- SQL Server 2005之PIVOT/UNPIVOT行列轉換(轉)SQLServer
- WORD及EXCEL行列轉換實現方法收藏(轉)Excel
- 【SQL 分析函式】wm_concat 行列轉換SQL函式
- 行列轉換 交叉表 (轉)
- Oracle-行列轉換Oracle
- MySQL行列轉換拼接MySql
- 行列轉換之大全~~~
- 試驗Oracle中實現行列轉換的方法(轉)Oracle
- oracle行列轉換-多行轉換成字串Oracle字串
- 行列轉換,列行轉換統計
- oracle行列轉換-行轉列Oracle
- oracle行列轉換-列轉行Oracle
- 使用vue實現行列轉換的一種方法。Vue
- mysql行列轉換詳解MySql
- Oracle 行列轉換 經典Oracle
- Oracle 行列轉換總結Oracle
- Oracle 行列轉換小結Oracle
- Oracle行列轉換總結Oracle
- Shell練習 行列轉換
- 複雜的行列轉換
- oracle行列轉換-字串轉換成多列Oracle字串
- oracle行列轉換-多列轉換成字串Oracle字串
- oracle11g pivot 行列轉換 SQL Server 2005OracleSQLServer
- 記錄一個行列轉換
- 行列轉換問題總結