Re: Return rows as 1 line?
From: jefftyzzer <jefftyzzer_at_sbcglobal.net>
Date: Thu, 20 May 2010 09:17:14 -0700 (PDT)
Message-ID: <50a6adf9-eb11-460f-8c86-aeeeac1238b2_at_s4g2000prh.googlegroups.com>
On May 17, 1:17 pm, The Magnet <a..._at_unsu.com> wrote:
> Hi,
>
> Ok, I am hoping this is now going to be nuts, but say I have data like
> this:
>
> ID PRODUCT SKU
> 1 ABC 123
> 1 DEF 456
> 2 XYZ 000
> 2 MNO 999
> 3 GHI 888
>
> And this is what I would need:
>
> 1,ABC:123,DEF:456
> 2,XYZ:000,MNO:999
> 3,GHI:888
>
> So, records that have a certain criteria would have certain columns
> concatenated. I'm thinking of putting together a dynamic query and
> then opening a cursor and selecting from it, creating the string as I
> go along.
>
> Any thoughts? I'm going to go with the dynamic cursor unless someone
> else has a better idea.
Date: Thu, 20 May 2010 09:17:14 -0700 (PDT)
Message-ID: <50a6adf9-eb11-460f-8c86-aeeeac1238b2_at_s4g2000prh.googlegroups.com>
On May 17, 1:17 pm, The Magnet <a..._at_unsu.com> wrote:
> Hi,
>
> Ok, I am hoping this is now going to be nuts, but say I have data like
> this:
>
> ID PRODUCT SKU
> 1 ABC 123
> 1 DEF 456
> 2 XYZ 000
> 2 MNO 999
> 3 GHI 888
>
> And this is what I would need:
>
> 1,ABC:123,DEF:456
> 2,XYZ:000,MNO:999
> 3,GHI:888
>
> So, records that have a certain criteria would have certain columns
> concatenated. I'm thinking of putting together a dynamic query and
> then opening a cursor and selecting from it, creating the string as I
> go along.
>
> Any thoughts? I'm going to go with the dynamic cursor unless someone
> else has a better idea.
Not sure if you've gotten far on your solution, but you may want to have a look at a Oct 27, 2006 posting titled "concatenating historical records" in the comp.databases.ibm-db2 group. In it, I mentioned two solutions I know of: one uses a recursive CTE (in Oracle you can add SYS_CONNECT_BY_PATH to this mix--see page 122 of Anthony Molinaro's _SQL Cookbook_, published by O'Reilly), and the other uses the XMLAGG function.
--Jeff Received on Thu May 20 2010 - 11:17:14 CDT