• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 235
  • Last Modified:

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    
0
ndwHombre
Asked:
ndwHombre
  • 2
  • 2
1 Solution
 
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
 
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

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now