Multi tool use

Oracle 11g SQL to split column data as mutiple column data

I have a requirement where data is as below

ID Date Comments
101 23/Jun/2018 RR: Insufficient resource
101 23/Jun/2018 Next week apply for leave
102 22/Jun/2018 NULL
103 21/Jun/2018 Go ahead


I need a Oracle sql to fetch above data as

ID DATE A_comments D_comments
101 23/Jun/2018 Next week apply for leave RR: Insufficient resource
102 22/Jun/2018
103 21/Jun/2018 Go ahead


Whenever comment is with prefix RR: it should go to D_comments and without prefix RR it should go to A_comments for a particular ID.

D_comments

A_comments

Help me to achieve this.

3 Answers
3

This query works for your sample data. But, if there are any other unknown scenarios as mentioned by Alex, please edit your question to add it.

SELECT
id,
date_t,
MAX(
CASE
WHEN comments LIKE 'RR:%' THEN NULL
ELSE comments
END
) a_comments,
MAX(
CASE
WHEN comments LIKE 'RR:%' THEN comments
END
) d_comments
FROM
t
GROUP BY
id,
date_t;


Demo

Try the below

WITH t
AS (SELECT 101 id_col,
'23/Jun/2018' date_col,
'RR: Insufficient resource' comments
FROM DUAL
UNION
SELECT 101 id_col,
'23/Jun/2018' date_col,
'Next week apply for leave' comments
FROM DUAL
UNION
SELECT 102 id_col, '22/Jun/2018' date_col, 'NULL' comments FROM DUAL
UNION
SELECT 103 id_col, '21/Jun/2018' date_col, 'Go ahead' comments
FROM DUAL)
( SELECT id_col,
date_col,
MAX (CASE WHEN comments NOT LIKE 'RR:%' THEN comments END)
a_comment,
MAX (CASE WHEN comments LIKE 'RR:%' THEN comments END) d_comment
FROM t
GROUP BY id_col, date_col)
ORDER BY id_col


SQL Fiddle Demo

You can "distribute" columns based on the value with the CASE statement:

CASE

SELECT id,
date,
case when comments not like 'RR:%' then comments end as A_comment,
case when comments like 'RR:%' then comments end as D_comment
FROM my_table;


The output would be

ID DATE A_comments D_comments
101 23/Jun/2018 Next week apply for leave
101 23/Jun/2018 RR: Insufficient resource
102 22/Jun/2018
103 21/Jun/2018 Go ahead


Therfore, you need to "fold" the columns into one row, for instance with MAX:

MAX

SELECT id,
date,
MAX(case when comments not like 'RR:%' then comments end) as A_comment,
MAX(case when comments like 'RR:%' then comments end) as D_comment
FROM my_table
GROUP BY id, date;


which would generate

ID DATE A_comments D_comments
101 23/Jun/2018 Next week apply for leave RR: Insufficient resource
102 22/Jun/2018
103 21/Jun/2018 Go ahead


EDIT:

To reflect your comment, to append all comments, you could use LISTAGG instead of MAX:

LISTAGG

MAX

SELECT id,
date,
LISTAGG(case when comments not like 'RR:%' then comments end, '-'
WITHIN GROUP (ORDER BY comments)) as A_comment,
MAX(case when comments like 'RR:%' then comments end) as D_comment
FROM my_table
GROUP BY id, date;


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.