CODEWITHSUNDEEP
sqloracle-databaseoracle10g
Shaji
Shaji

Reputation: 761

Merge Rows into One Row - oracle 10g

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

Upvotes: 0

Views: 1137

Answers (1)

wweicker
wweicker

Reputation: 4962

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;

Upvotes: 1

Related Questions