Combining Rows

bwells2004 · #1 (**permalink**) 09-01-06, 11:07

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.

Tyveleyn · #2 (**permalink**) 09-01-06, 16:31

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

Tyveleyn · #3 (**permalink**) 09-02-06, 03:56

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

Internet Services

Web Development

Digital Marketing

Consumer Tech