concatinate rows for all repeated values in another column

[srimitta]

Hi,
My source data looks like this in DB2:
COLA COLB
MIS-1 4 off Strips of clamp rubber
MIS-1 1 off roller bearing
MIS-2 1 off Heater rod
MIS-2 2 off 16mm dia x 299 ground shafts

How can I concatinate COLB for all repeated COLA values.
COLA COLB
MIS-1 4 off Strips of clamp rubber 1 off roller bearing
MIS-2 1 off Heater rod 2 off 16mm dia x 299 general shafts

Any help would be great.
Thanks
Srimitta

 

[stevexff]

This is a fairly common question, but I've never really understood why people always seem to want to denormalise data in this way. So it probably means it's for a management report of some kind.

While you may be able to do this in SQL it is usually easier to do it programmatically. In addition, wouldn't it be easier if the quantity was in a separate column (and even better if the description was actually an FK to another table)? Then you could sum the results by COLA and description, so you wouldn't get duplicate 'columns' when you denormalise the data?

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[Mercury2]

You could use the GROUP_CONCAT Built-In-Function if any though personally totally opposed.

 

[sathyarams]

This query should do the trick ...

in the final select part, select only the column list corresponding to the max of colnumber of the table

HTH
Sathyaram

with temp(colno,ts,tn,tc) as
(
select colno,tabschema,tabname, cast(colname as varchar(1000)) from syscat.columns
where tabname in ('TABLES','COLUMNS')   and tabschema='SYSSTAT' and colno=0
union all
select t.colno+1,tabschema,tabname,tc||','||colname from syscat.columns c,temp t where t.tn=c.tabname and t.ts=c.tabschema  a
nd c.colno=t.colno+1

)
select  * from temp

For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

&

http://www.idug.org/user/UserLogin.asp