Double LEFT JOIN on Same Table for Transpose

Question

IBM iNavigator (or generic/agnostic SQL):

I am working with an IBM system that saves comments in 25-character text chunks. I am trying to reassemble the first three segments of text chunks for each saved comment. The comment table has a comment header number (COMM_NO), a line sequence number (LINE_SQ), and the actual text (TXT). I'm looking at three example comments. Comment #1140 says "Customer product return after warranty expired. Needs quote issued." Comment #1408 is two segments, "Tried to call customer after update." Comment #2884 is short and only says "RMA #467". Table data looks like this [EDIT: corrected insert statement]:

CREATE TABLE mycomments
    ([COMM_NO] int, [LINE_SQ] int, [TXT] varchar(25))
;
INSERT INTO mycomments
    ([COMM_NO], [LINE_SQ], [TXT])
VALUES
    (1140, 1, 'Customer product return a'),
    (1140, 2, 'fter warranty expired. Ne'),
    (1140, 3, 'eds quote issued.'),
    (1408, 1, 'Tried to call customer af'),
    (1408, 2, 'ter update.'),
    (2884, 1, 'RMA #467');

And what I need is this:

COMM_NO  TXT1                      TXT2                      TXT3
-------  ------------------------- ------------------------- ------------------------- 
1140     Customer product return a fter warranty expired. Ne eds quote issued.
1408     Tried to call customer af ter update.               {null} 
2884     RMA #467                  {null}                    {null}

I have several tries on LEFT JOINing the table on itself twice using aliases. I'm not entirely there yet, but this is my closest try so far:

SELECT 
  comm1.COMM_NO
, comm1.TXT as TXT1
, comm2.TXT as TXT2
, comm3.TXT as TXT3
  FROM mycomments comm1
  LEFT JOIN mycomments AS comm2 ON comm2.COMM_NO = comm1.COMM_NO AND comm1.LINE_SQ=1 AND comm2.LINE_SQ=2
  LEFT JOIN mycomments AS comm3 ON comm3.COMM_NO = comm1.COMM_NO AND comm1.LINE_SQ=1 AND comm2.LINE_SQ=2 AND comm3.LINE_SQ=3

Actually I would really like to concat the three segments together, even if TXT3 or TXT2/TXT3 above are nulls like this:

COMM_NO  BIGTXT1
-------  --------------------------------------------------------------------------- 
1140     Customer product return after warranty expired. Needs quote issued.
1408     Tried to call customer after update.
2884     RMA #467

Any help is appreciated. Thanks!

Answer 1

First off, great job on providing the DDL and INSERT statement. The main issue you're having is your table order on your JOIN definition. Order matters on OUTER joins. Since you're issuing a LEFT join against comm1, that always needs to be first in your ON statement. In other words, you're saying "always give me data from comm1, and give me whatever is in my joined table if there is data there." So I re-wrote it as such:

SELECT 
  comm1.COMM_NO
, comm1.TXT as TXT1
, comm2.TXT as TXT2
, comm3.TXT as TXT3
  FROM mycomments comm1
  LEFT JOIN mycomments AS comm2 ON comm1.COMM_NO = comm2.COMM_NO AND comm2.LINE_SQ=2
  LEFT JOIN mycomments AS comm3 ON comm1.COMM_NO = comm3.COMM_NO AND comm3.LINE_SQ=3
  WHERE comm1.LINE_SQ = 1

Note - I also put the comm1.LINE_SQ = 1 criteria in the WHERE clause so you don't have to repeat it on the JOINs. I tested using your provided DDL and it works as expected.

If you want in one big column, you can do something like this for your SELECT statement:

comm1.TXT + CASE WHEN comm2.TXT IS NOT NULL THEN comm2.TXT ELSE '' END + CASE WHEN comm3.TXT IS NOT NULL THEN comm3.TXT ELSE '' END BIGTXT1