How to change my name into columns using sql-CodePudding

What is the best query to split name into columns?

ie. if my name is VELOCITY in one column.

How can i split it as

V
E
L
O
C
I
T
Y

CodePudding user response：

If you just have a single row then:

SELECT SUBSTR('VELOCITY', LEVEL, 1) AS ch
FROM   DUAL
CONNECT BY LEVEL <= LENGTH('VELOCITY');

Which outputs:

CH

V

E

L

O

C

I

T

Y

CH
V
E
L
O
C
I
T
Y

If you have multiple input rows then, from Oracle 12, you can use a LATERAL join:

SELECT c.ch
FROM   table_name t
       INNER JOIN LATERAL (
         SELECT SUBSTR(t.value, LEVEL, 1) AS ch
         FROM   DUAL
         CONNECT BY LEVEL <= LENGTH(t.value)
       ) c
       ON (t.value IS NOT NULL);

db<>fiddle here