Merge Rows into One Row - oracle 10g

Question

I have some data spread over two tables. The second table stores the data in line numbers. Is there a way I can show the line numbers in one row instead of many? See screenshot. Line Numbers jumbled

SELECT DISTINCT II.FC,
            II.GN,
            II.PB,
            II.DTI,
            TL.LINENUMBER,
            TL.TEXTLINE
FROM    (   ABC.ITD ITD
       INNER JOIN
          ABC.TEXTLINE TEXTLINE
       ON (ITD.DTI = TEXTLINE.TEXTID))
   INNER JOIN
      ABC.II II
   ON (II.ITEMID = ITD.ITEMID)
WHERE (II.FC = 'J') AND (TEXTLINE.TEXTLINE IS NOT NULL)
ORDER BY ITD.DTI ASC

Are you trying to concatenate the columns into a single string, or into a set of columns? And what do you want your actual result set to look like? — Clockwork-Muse
– Clockwork-Muse, Commented Oct 24, 2011 at 15:52
Yes, I am trying to concatenate the textline that is spread over four rows into one row. So my output should read:LANSOPRAZOLE | PREVACID CAP 15 MG | 3674 | DO NOT SUB LANLSOPRAZOLE OMEPRAZOLE IF PATIENT IS ALSO TA P&T FEB 2011: CLOPIDOGREL (PLAVIX) — Shaji
– Shaji, Commented Oct 24, 2011 at 16:01

wweicker · Accepted Answer · 2011-10-24 17:17:06Z

1

You'll need to create a function to look up and concatenate the text lines:

CREATE OR REPLACE FUNCTION FN_APPEND_TEXT(idText IN NUMBER) RETURN VARCHAR2
IS
   CURSOR crsText IS
      SELECT TL.LINENUMBER,
             TL.TEXTLINE
        FROM ABC.TEXTLINE TL
       WHERE TL.TEXTID = idText
         AND TL.TEXTLINE IS NOT NULL
       ORDER BY TL.LINENUMBER ASC;

   strReturn   VARCHAR2(4000);
BEGIN

   FOR recText IN crsText
   LOOP
      strReturn := strReturn || recText.TEXTLINE;
   END LOOP;

   RETURN strReturn;

END FN_APPEND_TEXT;

Then modify your SQL to call the function:

SELECT DISTINCT II.FC,
       II.GN,
       II.PB,
       II.DTI,
       FN_APPEND_TEXT(II.DTI) Instructions
  FROM ABC.ITD II
 WHERE II.FC = 'J'
 ORDER BY II.DTI ASC;

answered Oct 24, 2011 at 17:17

wweicker

4,9925 gold badges39 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Shaji Over a year ago

Thank you! Your help is highly apprecaited.

Collectives™ on Stack Overflow

Merge Rows into One Row - oracle 10g

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related