rows into column grouped by same ID

[Jack3d]

Im new to WebFocus and need a way to get multiple rows with same ID into a column separated by commas grouped by the ID.

--Before
DepId | ProId | ProCode
1 | 1 | PM
1 | 2 | IM
1 | 3 | SSO
1 | 4 | ECM
2 | 1 | PM
2 | 2 | IM
2 | 3 | SSO
3 | 2 | IM
3 | 3 | SSO
3 | 4 | ECM

--After
DepId | ProductList
1 | PM, IM, SSO, ECM
2 | PM, IM, SSO
3 | IM, SSO, ECM

Here is the SQL code example:
create table #testTable (
DepId int,
ProId int,
ProCode varchar(512)
)

insert into #testTable
select 1, 1, 'PM' union all
select 1, 2, 'IM' union all
select 1, 3, 'SSO' union all
select 1, 4, 'ECM' union all
select 2, 1, 'PM' union all
select 2, 2, 'IM' union all
select 2, 3, 'SSO' union all
select 3, 2, 'IM' union all
select 3, 3, 'SSO' union all
select 3, 4, 'ECM'

--Output before merging rows into grouped by coulumn
select * from #testTable

--Need WebFOCUS code for this output
Select
t1.DepId
,STUFF((Select ', ' + t.ProCode
From #testTable t
where t.DepId = t1.DepId
for XML Path('')
),1,2,'') ProductList
From
#testTable t1
Group by t1.DepId

drop table #testTable

 

[sxschech]

Not sure if this is what you are looking for as this isn't comma separated output,

[pre]
Product List
1 2 3 4
DEPID

--------------------------------------------------------------------------------

1 PM IM SSO ECM
2 PM IM SSO .
3 . IM SSO ECM

[/pre]

Here is the code as created in dev studio report painter:

Note: On the first line of code below, you will need to change the CONNECTION from ODSP to the connection your database uses for this example to run.

ENGINE SQLORA SET DEFAULT_CONNECTION ODSP
SQL SQLORA PREPARE SQLOUT FOR
select 1 as depid, 1 as proid, 'PM' as procode from dual union all
select 1, 2, 'IM' from dual union all
select 1, 3, 'SSO' from dual union all
select 1, 4, 'ECM' from dual union all
select 2, 1, 'PM' from dual union all
select 2, 2, 'IM' from dual union all
select 2, 3, 'SSO' from dual union all
select 3, 2, 'IM' from dual union all
select 3, 3, 'SSO' from dual union all
select 3, 4, 'ECM' from dual
END
TABLE FILE SQLOUT
SUM 
     PROCODE
BY  LOWEST DEPID
ACROSS LOWEST PROID AS 'Product List'
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
TYPE=REPORT,
     GRAPHCOLOR='GREEN',
$
TYPE=ACROSSVALUE,
     ACROSS=1,
     BACKCOLOR='WHITE',
$
TYPE=REPORT,
     OBJECT=MENU,
     COLOR='WHITE',
     HOVER-COLOR=RGB(66 70 73),
     BACKCOLOR=RGB(102 102 102),
     HOVER-BACKCOLOR=RGB(218 225 232),
     BORDER-COLOR='WHITE',
$
TYPE=REPORT,
     OBJECT=STATUS-AREA,
     COLOR='WHITE',
     BACKCOLOR=RGB(102 102 102),
$
TYPE=REPORT,
     OBJECT=CURRENT-ROW,
     HOVER-BACKCOLOR=RGB(218 225 232),
     BACKCOLOR=RGB(200 200 200),
$
TYPE=REPORT,
     OBJECT=CALC-AREA,
     COLOR='WHITE',
     BACKCOLOR=RGB(102 102 102),
$
ENDSTYLE
END

 

[Jack3d]

I need them all in single column and I got what I needed with this:

DEFINE FILE xxxx
PRODUCTS/A100V = IF DEPID EQ LAST DEPID THEN PRODUCTS || ', ' | PROCODE ELSE PROCODE
ELSE CLUSTER_PRODUCT_INFO.TBLPRODUCT.CODE;
END

TABLE FILE xxxx
SUM PRODUCTS
BY DEPID
END