Results 1 to 3 of 3

Thread: Combining Rows

  1. #1
    Join Date
    Sep 2006
    Posts
    1

    Unanswered: Combining Rows

    I have a result set that returns the following,

    casenum seq no text
    06-1000 0 This is my first line of text
    06-1000 1 my second line of text
    06-1000 2 and my third line of text
    06-2000 0 Text for a new case
    06-2000 1 with only two lines of text

    I want to return in informix

    06-1000 This is my first line of text my second line of text and my third
    line of text
    06-2000 Text for a new case with only two lines of text

    Is the above possible using SQL or do I need to write code to handle this? Any help would be appreciated.

  2. #2
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    Hi, you could do something like:
    Code:
    SELECT casenum, seq_no, trim(text) ||
    	(SELECT trim(text) FROM table 
             WHERE casenum = t.casenum
    	 AND seq_no = t.seq_no + 1) ||
    	(SELECT trim(text) FROM table 
             WHERE casenum = t.casenum
    	 AND seq_no = t.seq_no + 2)
    FROM table t
    WHERE seq_no = 1
    where the successive texts are retrieved with the subqueries in the SELECT clause. But I just tried it and it fails when there are less successive rows than subqueries. So this will only work if there is a fixed amount of rows per case and it's a shitty solution anyway, for one thing because of performance. I would definitely choose for some code wrapped around it to get what you want.

    Regards

  3. #3
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    Hi again, did some testing and concluded that you could do some more to get the desired output:
    Code:
    SELECT casenum, trim(text) ||
    	CASE (SELECT count(*)
    		FROM table WHERE casenum = t.casenum)
    	WHEN 2 THEN
    		(SELECT trim(text) FROM table 
       	         WHERE casenum = t.casenum
    		 AND seq_no = t.seq_no + 1)
    	WHEN 3 THEN
    		(SELECT trim(text) FROM table 
       	         WHERE casenum = t.casenum
    		 AND seq_no = t.seq_no + 1) ||
    		(SELECT trim(text) FROM table 
                     WHERE casenum = t.casenum
    		 AND seq_no = t.seq_no + 2)
    	ELSE
    		''
    	END CASE
    FROM table t
    WHERE seq_no = 0
    With this you still need to have a fixed maximum number of rows per case (or no successive text will be shown when exceeding the number of branches in CASE) but with the CASE statement you can anticipate with every occurrence. You need the concatenation operator '||' to get the same number of columns for each case in the SELECT clause.

    Regards
    Last edited by Tyveleyn; 09-02-06 at 06:38.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •