INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

rows into column grouped by same ID

rows into column grouped by same ID

(OP)
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

RE: rows into column grouped by same ID

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

 Product List 
 1 2 3 4 
DEPID     

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

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.

CODE

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 

RE: rows into column grouped by same ID

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close