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 |
|
itsmeeh
Starting Member
7 Posts |
 Posted - 2003-06-16 : 12:52:13
|
| Hello. This is a continuation to my intial question on row values to columns...I have the below script and the problem i'm having now is that it's not giving me the result set I want. The company name is duplicating to the number of campuses. For example: CompanyID | CompanyName | Campus1 | Campus2 | Campus3 | Campus4 101 | Southwest | 59 | NULL | NULL | NULL 101 | Southwest | NULL | 1569 | NULL | NULL I want the following result set: CompanyID | CompanyName | Campus1 | Campus2 | Campus3 | Campus4 101 | Southwest | 59 | 1569 | NULL | NULL How do I get companyname to appear only once? Any suggestions: declare @campus table (CompanyID int, CompanyName varchar(200), CampusID int) insert into @campus select racac.CompanyId, ac.Companyname, CampusID from racompanyaCampus as racac inner join acompany as ac on ac.companyId=racac.companyid inner join acompany as ac2 on ac2.companyid=racac.campusid order by ac.companyName select CompanyID, CompanyName, MAX(case when CampusNumber = 1 then CampusID else Null END) as Campus1, MAX(case when CampusNumber = 2 then CampusID else Null END) as Campus2, MAX(case when CampusNumber = 3 then CampusID else Null END) as Campus3, MAX(case when CampusNumber = 4 then CampusID else Null END) as Campus4 FROM ( select CompanyID, CompanyName, CampusID, (select count(*) from @campus b where b.companyID = a.companyID and b.campusID <= a.campusID) as CampusNumber from @campus a ) b GROUP BY CompanyID, CompanyName, CampusID Thanks so much,Jen |
|
|
robvolk
Most Valuable Yak
15732 Posts |
|
|
|
|
|
|
|
Removing duplicate row values