Combining muliple columns into one field

Hi there,<br />
<br />
I'm looking for some ideas of what I want to accomplish.  On the iSeries, I did
a DSPFFD to an outfile for all the objects of a given library so I can see the
field names associated with a file.<br />
<br />
ie.<br />
SELECT WHFLDI FROM LIB.DSPFFDX WHERE WHFILE = 'DFHRREP' <br />
<br />
Results in:<br />
<br />
HRM9CD    	<br />
HRUVNB    	<br />
HRMFCD    	<br />
HRVCNB    	<br />
HRUWNB    	<br />
HRN9CD    	<br />
HRNCCD    	<br />
<br />
What I would like to do is return it in this format, into a variable or as a
result of the select:<br />
<br />
HRM9CD, HRUVNB, HRMFCD, HRVCNB, HRUWNB, HRN9CD, HRNCCD<br />
<br />
Depending on the file, there could be more or less fields.  Ultimately, I want
to pass this 'string' into a Stored Procedure, etc.<br />
<br />

Hi,<br />
<br />
there is no native function to solve your problem.<br />
You need to write an User Defined Function. Just have a look at the following
article, I assume it delivers a solution for your problem:<br />
<br />
Use SQL User-Defined Functions to Avoid Data Repetition Problems <br />
<a class="jive-link-external"
href="http://www.itjungle.com/fhg/fhg121207-story01.html">http://ww
w.itjungle.com/fhg/fhg121207-story01.html</a><br />
<br />

Hi,<br />
<br />
there is no native function to solve your problem.<br />
You need to write an User Defined Function. Just have a look at the following
article, I assume it delivers a solution for your problem:<br />
<br />
Use SQL User-Defined Functions to Avoid Data Repetition Problems <br />
<a class="jive-link-external"
href="http://www.itjungle.com/fhg/fhg121207-story01.html">http://ww
w.itjungle.com/fhg/fhg121207-story01.html</a><br />
<br />

Hi Birgitta,<br />
<br />
Thanks for the link.  I'll give it a try!<br />
<br />

   Per mention of iSeries versus System i, your OS may be too old to 
support it, but I offer...

   An example for a single file DFHRREP in library LIB.  As coded, it 
must be run by a user with special authority *ALLOBJ.  Use a VIEW in 
place of QADBIFLD to remove that requirement; the user creating the VIEW 
must have that authority.

<code>
   with
    fldlstrow (dbilib, dbifil, dbifmt, dbifld, dbifmp, dbipos) as
     ( select dbilib, dbifil, dbifmt, varchar(dbifld), dbifmp, dbipos
        from qsys.qadbifld
        where dbilib='LIB' and dbifil='DFHRREP' )
   ,fldlstcol (dbilib, dbifil, dbifmt, dbifld, dbifmp, dbipos) as
     ( select  dbilib, dbifil, dbifmt, dbifld, dbifmp, dbipos
        from fldlstrow where dbipos=1
     union all
       select  c.dbilib, c.dbifil, c.dbifmt,
               strip(c.dbifld) concat ', ' concat strip(r.dbifld)
              ,c.dbifmp, c.dbipos+1
        from fldlstcol c, fldlstrow r
        where c.dbipos+1 = r.dbipos )
   select dbilib, dbifil, dbifld as fldlst
    from fldlstcol where dbipos = (select max(dbipos) from fldlstrow)
</code>

<code>
  DBILIB DBIFIL  FLDLST
  LIB    DFHRREP HRM9CD, HRUVNB, HRMFCD, HRVCNB, HRUWNB, HRN9CD, HRNCCD
  ********  End of data  ********
</code>

Regards, Chuck
-- 
  All comments provided "as is" with no warranties of any kind 
whatsoever and may not represent positions, strategies, nor views of my 
employer

omar.pricca@marks.com wrote:
> I'm looking for some ideas of what I want to accomplish.
> On the iSeries, I did a DSPFFD to an outfile for all the objects of
> a given library so I can see the field names associated with a file.
> 
> i.e.  SELECT WHFLDI FROM LIB.DSPFFDX WHERE WHFILE = 'DFHRREP'
> 
> Results in:
> 
> HRM9CD
> HRUVNB
> HRMFCD
> HRVCNB
> HRUWNB
> HRN9CD
> HRNCCD
> 
> What I would like to do is return it in this format, into a variable
> or as a result of the select:
> 
> HRM9CD, HRUVNB, HRMFCD, HRVCNB, HRUWNB, HRN9CD, HRNCCD
> 
> Depending on the file, there could be more or less fields.
> Ultimately, I want to pass this 'string' into a Stored Procedure, etc.
>