Re: SQL Q: Concatenate multiple rows on same column.
* Nick Heppleston I have a concatenation problem and I was wondering if somebody might be able to offer some help :-) I can try. :) I have the following table structure holding product long descriptions: Part No (pn)Sequence (seq) Long Description (long_desc) --- --- HL145010 This is part of a long description and HL145020 it overlaps onto several lines. I'm HL145030 having difficulty writing the SQL that HL145040 is needed to make this work... I need to write SQL that will give me the following output: Part No Long Description --- HL1450This is part of a long description and it overlaps onto several lines. I'm having difficulty writing the SQL that is needed to make this work... You should use a single row with a wider column, for instance of type TEXT. URL: http://www.mysql.com/doc/en/Column_types.html#IDX1075 URL: http://www.mysql.com/doc/en/BLOB.html#IDX1100 (Essentially it a GROUP BY on the part number with an ORDER by on the sequence field). However, I know how to concatenate multiple columns on the same row (using the CONCAT or CONCAT_WS functions) but I'm at a loss over how to concatenate the same column over multiple _rows_ I am aware of the GROUP_CONCAT function, but we are currently running MySQL 4.0.13 (this functionality is only available on a vers 4.1) If any one has a suggestion as how I should go about this one I would really appreciate it. Try using a user variable: SET @a=; SELECT @a:=CONCAT(@a,' ',long_desc) FROM YourTable; SELECT @a; URL: http://www.mysql.com/doc/en/Variables.html Beware that there are problems with user variables if you are using replication: URL: http://www.mysql.com/doc/en/Replication_Features.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Q: Concatenate multiple rows on same column.
* Roger Baklund [...] SET @a=; SELECT @a:=CONCAT(@a,' ',long_desc) FROM YourTable; SELECT @a; Sorry, my test table only contained records for a single product... you would need something like this: SELECT @a:=CONCAT(@a,' ',long_desc) FROM YourTable WHERE pn = HL1450 ORDER BY seq; I don't know how to combine this with GROUP BY and ORDER BY. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Q: Concatenate multiple rows on same column.
On Thu, Aug 21, 2003 at 03:30:11PM +, Nick Heppleston wrote: I have a concatenation problem and I was wondering if somebody might be able to offer some help :-) Hi Nick, Your problem is a formatting problem, not a concatenation problem; i.e. SQL concatenation wasn't intended to solve this problem. SQL has very limited formatting capabilities. You need to send the SQL output to an application that will format it for you and generate a report. You can write the app yourself (PHP, Pearl, C, etc, etc) or you can use an existing app with report-formatting capabilities. Bob Hall -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
