SQL - 将单列分成多列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24033336/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL - Divide single column in multiple columns
提问by Lc0rE
I have the following SQL question:
我有以下 SQL 问题：
How to divide a column (text inside) using the SELECT command into two separate columns with split text?
如何使用 SELECT 命令将一列（里面的文本）分成两个带有拆分文本的单独列？
I need to separate the text-data, using the space character. I know it is better to put an example to make it easy. So:
我需要使用空格字符分隔文本数据。我知道最好举个例子来简化。所以：
SELECT COLUMN_A FROM TABLE1
output:
输出：
COLUMN_A
-----------
LORE IPSUM
desired output:
所需的输出：
COLUMN_A COLUMN_B
--------- ----------
LORE IPSUM
Thank you all for the help.
谢谢大家的帮助。
回答by Nishanthi Grashia
Depends on the consistency of the data - assuming a single space is the separator between what you want to appear in column one vs two:
取决于数据的一致性 - 假设单个空格是您想要出现在第一列与第二列中的分隔符：
WITH TEST_DATA AS
(SELECT 'LOREM IPSUM' COLUMN_A FROM DUAL)
SELECT SUBSTR(t.COLUMN_A, 1, INSTR(t.COLUMN_A, ' ')-1) AS COLUMN_A,
SUBSTR(t.COLUMN_A, INSTR(t.COLUMN_A, ' ')+1) AS COLUMN_B
FROM test_data T;
You can also use below query with REGEX:
您还可以在 REGEX 中使用以下查询：
WITH TEST_DATA AS
(SELECT 'LOREM IPSUM' COLUMN_A FROM DUAL)
SELECT REGEXP_SUBSTR(t.COLUMN_A, '[^ ]+', 1, 1) COLUMN_A,
REGEXP_SUBSTR(t.COLUMN_A, '[^ ]+', 1, 2) COLUMN_B
FROM test_data T;
Oracle 10g+ has regex support, allowing more flexibility depending on the situation you need to solve. It also has a regex substring method...
Oracle 10g+ 支持正则表达式，根据您需要解决的情况提供更大的灵活性。它还有一个正则表达式子字符串方法......
EDIT:3 WORDS SPLIT:
编辑：3 字拆分：
WITH TEST_DATA AS
(SELECT 'LOREM IPSUM DIMSUM' COLUMN_A FROM DUAL)
SELECT REGEXP_SUBSTR(t.COLUMN_A, '[^ ]+', 1, 1) COLUMN_A,
REGEXP_SUBSTR(t.COLUMN_A, '[^ ]+', 1, 2) COLUMN_B,
REGEXP_SUBSTR(t.COLUMN_A, '[^ ]+', 2, 3) COLUMN_C
FROM test_data T;
Reference:
参考：
回答by Serpiton
The solution can be generalized using a counter and the PIVOToperator, the counter to get the word number and the PIVOTto change rows to columns
该解决方案可以使用计数器和PIVOT运算符进行概括，计数器获取单词编号并将PIVOT行更改为列
WITH Counter (N) AS (
SELECT LEVEL FROM DUAL
CONNECT BY LEVEL <= (SELECT MAX(regexp_count( COLUMN_A, ' ')) + 1
FROM Table1)
)
SELECT Word_1, Word_2, Word_3, Word_4
FROM (SELECT t.COLUMN_A
, c.N N
, REGEXP_SUBSTR(t.COLUMN_A, '[^ ]+', 1, c.N) Word
FROM Table1 t
LEFT JOIN Counter c ON c.N <= regexp_count( COLUMN_A, ' ') + 1) b
PIVOT
(MAX(Word) FOR N IN (1 Word_1, 2 Word_2, 3 Word_3, 4 Word_4)) pvt
But that have a fixed columns list in the PIVOTdefinition, to really have a general query a dynamic pivot or a PIVOT XMLis needed
但是，有一个固定的列列表中PIVOT的定义，真的有一个通用查询动态支点或PIVOT XML需要
