asked on

Select Statement to Group Data and Concate 2 Columns

This question is an extension of my other question at https://www.experts-exchange.com/questions/23124728/Select-Statement-to-Group-Data.html

I have a table called VTM that looks like this:

VTMIDVT MSBULLETIN OS
---------- ------------- -----
442 MS03-001 XP
4368 MS07-017 XP
4389 MS07-017 Vista
4448 MS07-019 XP
4449 MS07-021 Vista
4517 MS07-021 XP

I need a select statement that will group the bulletins together and concate the VTMID's like this and add columns for XP and Vista like this:

VTMIDVT MSBULLETIN XP Vista
--------- ------------- ----- ------

442 MS03-001 Yes No
4368, 4389 MS07-017 Yes Yes
4448 MS07-019 Yes No
4449, 4517 MS07-021 Yes Yes

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ndwHombre

ASKER

angelIII, Thanks, I'm going to accept the above as the answer but I have to throw this onto an Oracle DB and I'm getting an "Invalid Number" on the following query. Any idea as to what I'm doing wrong?

declare
res varchar(100);
begin
Select COALESCE(res, ',') + VTMIDVT into res FROM CITI_VTM_NEW_TEMP WHERE MSBULLETIN = 'MS03-001' GROUP BY VTMIDVT;
end;

Guy Hengel [angelIII / a3]

in oracle, you use || to concatenate strings, my code was for ms sql server...
I fear that in oracle, the trick I used for ms sql will not work that way.

see this article for how to do it in oracle:
http://halisway.blogspot.com/2006/08/oracle-groupconcat-updated-again.html

ndwHombre

ASKER

angelIII, Thanks for you comments but the analytical functions as well as the "sys_connect_by_path" function are confusing me. Any additional help would be greatly appreciated.