Select Statement to Group Data

This question is similar to my other question at http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_23126621.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 show the VTMIDVT for the OS if it exists or "None" if it doesn't:

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

MS03-001       442      None
MS07-017       4368    4389
MS07-019       4448    None
MS07-021       4449    4517



I trying this statement, but it is giving the results below:

SELECT MSBULLETIN
,MAX(CASE WHEN OS = 'XP' THEN Cast(VTMid as varchar) ELSE 'None' END) XP,
MAX(CASE WHEN OS = 'Vista' THEN Cast(VTMid as varchar) ELSE 'None' END) Vista
FROM VTM
GROUP BY MSBULLETIN

MSBULLETIN     XP     Vista    
-------------  -----  --------
MS03-001       442    None    
MS07-017       None   None    
MS07-019       4448   None    
MS07-021       None   None    
ndwHombreAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David ToddSenior DBACommented:
Hi,

SELECT
      MSBULLETIN
      , isnull( cast( MAX( CASE WHEN OS = 'XP' THEN VTMid END ) as varchar ), 'none' ) XP
      , isnull( cast( MAX( CASE WHEN OS = 'Vista' THEN VTMid END ) as varchar ), 'none' ) Vista
FROM VTM
GROUP BY MSBULLETIN

HTH
  David
use tempdb
go
 
if object_id( N'tempdb..#VTM', N'U' ) is not null 
	drop table #VTM;
	
create table #VTM 
	(
	VTMid int
	, MSBulletin varchar( 10 )
	, OS varchar( 10 )
	)
	
insert #VTM( VTMid, MSBulletin, OS )
select 442, 'MS03-001', 'XP'
union all select 4368, 'MS07-017', 'XP'    
union all select 4389, 'MS07-017', 'Vista'
union all select 4448, 'MS07-019', 'XP'    
union all select 4449, 'MS07-021', 'Vista'
union all select 4517, 'MS07-021', 'XP'    
 
select *
from #VTM
 
SELECT 
	MSBULLETIN
	, isnull( cast( MAX( CASE WHEN OS = 'XP' THEN VTMid END ) as varchar ), 'none' ) XP
	, isnull( cast( MAX( CASE WHEN OS = 'Vista' THEN VTMid END ) as varchar ), 'none' ) Vista
FROM #VTM
GROUP BY MSBULLETIN

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David ToddSenior DBACommented:
Hi

Explanation: I figure that the varchar value 'none' was getting a higher value than the number 4389. So I did the max as an integer, as null will be eliminated from max() and other aggrigates as long as there are row(s) in the input.

Why do you want 'none' rather than 'null'? To me the results look very similar and the code is a lot easier to read - no cast and no isull ...

Cheers
  David
0
TreadHeadCommented:
If you're using Oracle, then I'm not sure on the syntax, but you might try the following:

SELECT VTM.MSBULLETIN, CASE WHEN XP.VTMIDVT is NULL THEN 'None' ELSE CAST(XP.VTMIDVT as varchar) end as XP,
	CASE WHEN Vista.VTMIDVT is Null THEN 'None' ELSE CAST(Vista.VTMIDVT as varchar) end as Vista
FROM VTM
	LEFT JOIN (SELECT MSBULLETIN, Max(VTMIDVT) as VTMIDVT FROM VTM
				WHERE OS = 'XP'
				GROUP BY MSBULLETIN) XP on VTM.MSBULLETIN = XP.MSBULLETIN
	LEFT JOIN (SELECT MSBULLETIN, Max(VTMIDVT) as VTMIDVT FROM VTM
				WHERE OS = 'Vista'
				GROUP BY MSBULLETIN) Vista on VTM.MSBULLETIN = Vista.MSBULLETIN
GROUP BY VTM.MSBULLETIN, XP.VTMIDVT, VISTA.VTMIDVT

Open in new window

0
TreadHeadCommented:
Scratch mine -- I like dtodd's better.
0
ndwHombreAuthor Commented:
Thanks, I'm gonna go w/ the null values as output so I'm using the following.

SELECT
      MSBULLETIN
      , MAX( CASE WHEN OS = 'XP' THEN VTMid END )  XP
      , MAX( CASE WHEN OS = 'Vista' THEN VTMid END )  Vista
FROM VTM
GROUP BY MSBULLETIN
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.