How to Manipulate the data as - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to Manipulate the data as

Author

Topic

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-07 : 01:13:48

Hi everyone!

Hope you are doing well and wish you have a good day! :)

Please go through the following data and let me know how can i get the result set as mentioned below:

create table universities (UniversityID int, UniversityName varchaR(100))
Create table URegions (ID int, UniversityID int, RegionID int)

Insert into universities values (1,'abc')
Insert into universities values (2,'xyz')

Insert into URegions values ( 1,1,1)
Insert into URegions values ( 2,1,2)
Insert into URegions values ( 3,1,4)

Insert into URegions values ( 5,2,1)
Insert into URegions values ( 6,2,3)

Select
U.UniversityName
,(Case when UR.RegionID=1 then 'Yes' else 'No' End) as Region1
,(Case when UR.RegionID=2 then 'Yes' else 'No' End) as Region2
,(Case when UR.RegionID=3 then 'Yes' else 'No' End) as Region3
,(Case when UR.RegionID=4 then 'Yes' else 'No' End) as Region4
from universities U
inner Join URegions UR on U.UniversityID=UR.UniversityID

The result set of the above query is:

UniversityName Region1 Region2 Region3 Region4
abc Yes No No No
abc No Yes No No
abc No No No Yes
xyz Yes No No No
xyz No No Yes No

However i want the result set should be like

UniversityName Region1 Region2 Region3 Region4
abc Yes Yes No Yes
xyz Yes No Yes No

many thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-01-07 : 01:56:25

[code]
Select
U.UniversityName
,max(Case when UR.RegionID=1 then 'Yes' else 'No' End) as Region1
,max(Case when UR.RegionID=2 then 'Yes' else 'No' End) as Region2
,max(Case when UR.RegionID=3 then 'Yes' else 'No' End) as Region3
,max(Case when UR.RegionID=4 then 'Yes' else 'No' End) as Region4
from universities U
inner Join URegions UR on U.UniversityID=UR.UniversityID
group by U.UniversityName
[/code]

KH
[spoiler]Time is always against us[/spoiler]

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-07 : 02:06:42

@KH Many thanks!

Cheers!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources