Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
makis_best
Starting Member
8 Posts |
 Posted - 2015-04-12 : 06:23:01
|
| HiI have a table with records like that.Group | ValueTeam 1 | 0Team 1 | 0Team 1 | 1Team 1 | 1Team 2 | 0Team 2 | 0Team 2 | 0I want a script that return 0 if all the values of the group are 0 and return 1 if the records of the value is mixed with 0 and 1.Is that possible?Please help.Thank you.Sorry for my bad English |
|
|
stepson
Aged Yak Warrior
545 Posts |
Posted - 2015-04-12 : 10:32:04
|
| [code]DECLARE @tSample AS TABLE ([Group] VARCHAR(30) ,[Value] TINYINT)INSERT INTO @tSample([Group],[Value])SELECT 'Team 1', 0 UNION ALLSELECT 'Team 1', 0 UNION ALLSELECT 'Team 1', 1 UNION ALLSELECT 'Team 1', 1 UNION ALLSELECT 'Team 2', 0 UNION ALLSELECT 'Team 2', 0 UNION ALLSELECT 'Team 2', 0SELECT [Group] ,CASE WHEN SUM([Value])= 0 THEN 0 ELSE 1 END AS [Value]FROM @tSampleGROUP BY [Group][/code]it this the desire output ?[code]Group ValueTeam 1 1Team 2 0[/code]sabinWeb MCP |
 |
|
|
stepson
Aged Yak Warrior
545 Posts |
Posted - 2015-04-12 : 10:47:50
|
You can use the EXISTS ...SELECT [Group] ,CASE WHEN EXISTS (SELECT * FROM @tSample AS B WHERE A.[Group] = B.[Group] AND B.[Value]= 1 ) THEN 1 ELSE 0 END [Value]FROM @tSample AS AGROUP BY [Group] sabinWeb MCP |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2015-04-12 : 11:02:07
|
Or the simplest approach?SELECT [Group], MAX([Value])FROM dbo.Table1GROUP BY [Group];
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
|
|
|
|
|
|
Return a record depends of group result