Select Statement to Group Data and Concate 2 Columns

This question is an extension of my other question at http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_23124728.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
ndwHombreAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
the code was:
SELECT MSBULLETIN
, MAX(CASE WHEN OS = 'XP' THEN 'Yes' ELSE 'No' END) XP
, MAX(CASE WHEN OS = 'Vista' THEN 'Yes' ELSE 'No' END) Vista
FROM VTM
GROUP BY MSBULLETIN


now, please create the following function:

CREATE FUNCTION dbo.ConcatVTMIDVT(@MSBULLETIN  varchar(100))
RETURNS VARCHAR(1000)
AS
BEGIN
  DECLARE @res VARCHAR(1000)
  SELECT @res = COALESCE(@res + ',','') + VTMIDVT      
   FROM VTM
  WHERE MSBULLETIN = @MSBULLETIN  
  GROUP BY VTMIDVT      

  RETURN @res
END

and your query will be:

SELECT MSBULLETIN
, dbo.ConcatVTMIDVT(MSBULLETIN) VTMIDVT
, MAX(CASE WHEN OS = 'XP' THEN 'Yes' ELSE 'No' END) XP
, MAX(CASE WHEN OS = 'Vista' THEN 'Yes' ELSE 'No' END) Vista
FROM VTM
GROUP BY MSBULLETIN

0
 
ndwHombreAuthor Commented:
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;
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
0
 
ndwHombreAuthor Commented:
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.
0
All Courses

From novice to tech pro — start learning today.