SELECT CASE WHEN MOD (r, 2) = 1 THEN name ELSE address END AS text
(
SELECT ROW_NUMBER () OVER () AS r, t0.*
FROM
(
SELECT * FROM mytable
UNION ALL
SELECT * FROM mytable
ORDER BY pkey
) t0
) t1
2) Use a Cartesian cross join to replicate the data and again use the CASE operator to select the correct column;
SELECT CASE idx
WHEN 1 THEN name
WHEN 2 THEN address END AS text
FROM
(
VALUES (1), (2)
) t0 (idx)
CROSS JOIN mytable
ORDER BY pkey, idx;
3) Select the desired fields from the table using UNION ALL and a sort to structure them properly.
SELECT text
FROM
(
SELECT pkey, 1 AS rn, name AS text FROM mytable
UNION ALL
SELECT pkey, 2 AS rn, address FROM mytable
) t0
ORDER BY pkey, rn
[/step]
SELECT text
FROM
(
SELECT pkey, 1 AS rn, LTRIM (SUBSTR (name, LOCATE (',', name) + 1)) AS text FROM mytable
UNION ALL
SELECT pkey, 2 AS rn, SUBSTR (name, 1, LOCATE (',', name) - 1)) FROM mytable
UNION ALL
SELECT pkey, 3 AS rn, address FROM mytable
) t0
ORDER BY pkey, rn
CREATE TABLE sentences
(
snum INTEGER GENERATED BY DEFAULT AS IDENTITY,
sentence VARCHAR (200)
);
CREATE TABLE words
(
snum INTEGER, -- sentence number
wordnum INTEGER, -- word number in the sentence
word VARCHAR (100) -- word being saved
);
INSERT INTO sentences (sentence) VALUES ('This is a multiword string');
WITH pquery (snum, wordnum, word, remainder)
AS
(
SELECT base.snum, 1,
CASE WHEN LOCATE (' ', sentence) > 0 THEN
SUBSTR (sentence, 1, LOCATE (' ', sentence) - 1)
ELSE
sentence
END word,
CASE WHEN locate (' ', sentence) > 0 THEN
LTRIM (SUBSTR (sentence, LOCATE (' ', sentence) + 1))
ELSE
NULL
END remainder
FROM sentences base
UNION ALL
SELECT snum, wordnum + 1,
CASE WHEN LOCATE (' ', remainder) > 0 then
SUBSTR (remainder, 1, LOCATE (' ', remainder) - 1)
ELSE
remainder
END word,
CASE WHEN LOCATE (' ', remainder) > 0 THEN
LTRIM (SUBSTR (remainder, LOCATE (' ', remainder) + 1))
ELSE
NULL
END remainder
FROM pquery t0
WHERE t0.remainder IS NOT NULL
)
SELECT *
FROM pquery pq;
WITH ... SELECT * FROM pquery;
snum wordnum word remainder
1 1 This is a multiword string
1 2 is a multiword string
1 3 a multiword string
1 4 multiword string
1 5 string NULL
WITH pquery (snum, wordnum, word, remainder)
AS
(
SELECT base.snum, 1,
CASE WHEN LOCATE (' ', sentence) > 0 THEN
SUBSTR (sentence, 1, LOCATE (' ', sentence) - 1)
ELSE
sentence
END word,
CASE WHEN locate (' ', sentence) > 0 THEN
LTRIM (SUBSTR (sentence, LOCATE (' ', sentence) + 1))
ELSE
NULL
END remainder
FROM sentences base
UNION ALL
SELECT snum, wordnum + 1,
CASE WHEN LOCATE (' ', remainder) > 0 then
SUBSTR (remainder, 1, LOCATE (' ', remainder) - 1)
ELSE
remainder
END word,
CASE WHEN LOCATE (' ', remainder) > 0 THEN
LTRIM (SUBSTR (remainder, LOCATE (' ', remainder) + 1))
ELSE
NULL
END remainder
FROM pquery t0
WHERE t0.remainder IS NOT NULL
)
INSERT INTO words
SELECT snum, wordnum, word
FROM pquery pq
ORDER BY snum, wordnum;
INSERT INTO sentences (sentence) VALUES ('This multiword string has variable spacing');
INSERT INTO sentences (sentence) VALUES ('Short sentence');
WITH pquery (snum, wordnum, word, remainder)
AS
(
SELECT base.snum, 1,
CASE WHEN LOCATE (' ', sentence) > 0 THEN
SUBSTR (sentence, 1, LOCATE (' ', sentence) - 1)
ELSE
sentence
END word,
CASE WHEN locate (' ', sentence) > 0 THEN
LTRIM (SUBSTR (sentence, LOCATE (' ', sentence) + 1))
ELSE
NULL
END remainder
FROM sentences base
UNION ALL
SELECT snum, wordnum + 1,
CASE WHEN LOCATE (' ', remainder) > 0 then
SUBSTR (remainder, 1, LOCATE (' ', remainder) - 1)
ELSE
remainder
END word,
CASE WHEN LOCATE (' ', remainder) > 0 THEN
LTRIM (SUBSTR (remainder, LOCATE (' ', remainder) + 1))
ELSE
NULL
END remainder
FROM pquery t0
WHERE t0.remainder IS NOT NULL
)
SELECT *
FROM pquery pq;
SNUM WORDNUM WORD REMAINDER
1 1 This is a multiword string
2 1 This multiword string has variable spacing
3 1 Short sentence
1 2 is a multiword string
2 2 multiword string has variable spacing
3 2 sentence NULL
1 3 a multiword string
2 3 string has variable spacing
1 4 multiword string
2 4 has variable spacing
1 5 string NULL
2 5 variable spacing
2 6 spacing NULL
WITH pquery (snum, day, code, remainder)
...
SELECT pq.snum, CASE WHEN pq.code = 'P' THEN 'Present' ELSE 'Detention is in someone''s future' END AS status
FROM pquery pq
WHERE pq.snum = {student id}
AND pq.day = 17;
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (2)
Commented:
Author
Commented:And welcome to Experts Exchange! :)
Kent