CODEWITHSUNDEEP
variablesloopsselectplsqlsubstr
user3317443
user3317443

Reputation: 1

PL/SQL Select - Covert column data to rows 1 char per row

I am trying to create a SQL statement that will take eight 250-column fields from a table and convert them to 2000 rows with row numbers.
I have no idea how to read character by character (most of the characters being blank). How do I use a variable that increases the value by one? I tried Select substr(myFieldA,varA,1) from myTable but it doesn't like the variable, also how do I do the looping statement.

eg YYN YN N

I'd like it to display as follows with row numbers.

001 Y
002 Y
003 N
004 
005 
006 Y
007 N
008
009 N
010

Upvotes: 0

Views: 153

Answers (1)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60312

You can split up one of the fields with this query:

SELECT 'A' Field, r Col, SUBSTR(myFieldA,r,1) Val
FROM myTable
    ,(SELECT ROWNUM r FROM DUAL CONNECT BY LEVEL <= 250);

You can do the same for all 8 columns, e.g.:

SELECT 'A' Field, r Col, SUBSTR(myFieldA,r,1) Val
FROM myTable
    ,(SELECT ROWNUM r FROM DUAL CONNECT BY LEVEL <= 250)
UNION ALL
SELECT 'B' Field, r Col, SUBSTR(myFieldB,r,1) Val
FROM myTable
    ,(SELECT ROWNUM r FROM DUAL CONNECT BY LEVEL <= 250)
UNION ALL
... etc....
SELECT 'H' Field, r Col, SUBSTR(myFieldH,r,1) Val
FROM myTable
    ,(SELECT ROWNUM r FROM DUAL CONNECT BY LEVEL <= 250)
;

Upvotes: 0

Related Questions