Give this a try.
select
sys.Netbios_Name0,
isnull(max(ou.System_OU_Name0), SC.System_Container_Name0)
from
v_R_System_Valid sys
left outer join v_RA_System_SystemOUName ou on sys.ResourceID =
ou.ResourceID
left outer join dbo.v_RA_System_SystemContainerName SC on sys.ResourceID
= SC.ResourceID
group by
sys.Netbios_Name0,
SC.System_Container_Name0
Order by
sys.Netbios_Name0
Garth Jones
Chief Architect
www.Enhansoft.com<http://www.enhansoft.com/>
[Description: Description:
cid:[email protected]]<http://www.enhansoft.com/>
Enhancing Your Business
[Description: Description: Description: Description: Description: Description:
Description:
cid:[email protected]]<http://be.enhansoft.com/>[Description:
Description: Description: Description: Description: Description: Description:
cid:[email protected]]<https://twitter.com/enhansoft>[Description:
Description: Description: Description: Description: Description: Description:
cid:[email protected]]<http://www.facebook.com/EnhansoftInc>[Description:
Description: Description: Description: Description: Description: Description:
Description: Description: Description: Description: Description: Description:
Description: Description: Enhansoft's YouTube
Page]<http://www.youtube.com/user/Enhansoft/videos>[Description: Description:
Description: Description: Description: Description: Description:
cid:[email protected]]<http://myitforum.com/myitforumwp/community/groups/enhansoft/>
See us at booth #1240<http://northamerica.msteched.com/#fbid=poJHUxb29bD>
[TechEdExhibitor Button]<http://northamerica.msteched.com/#fbid=poJHUxb29bD>
Ask Me About Our Free SSRS
Reports<http://www.enhansoft.com/pages/Free-SSRS-Reports.aspx>
From: [email protected] [mailto:[email protected]] On
Behalf Of Daniel Ratliff
Sent: Tuesday, April 22, 2014 11:32 AM
To: [email protected]
Subject: [mssms] RE: Get the SYSTEM_OU_NAME without the duplicate rows
I literally had the same issue last week, and just filtered in Excel for the
sake of time. Would love to see some SQL magic to do the trick.
Daniel Ratliff
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]] On Behalf Of Krueger, Jeff
Sent: Tuesday, April 22, 2014 11:25 AM
To: [email protected]<mailto:[email protected]>
Subject: [mssms] Get the SYSTEM_OU_NAME without the duplicate rows
Does anyone have any SQL Fu handy that will give you the longest value in the
dbo.v_RA_System_SystemOUName? Trying to put together a report where I want to
add the OU a system is in but that view has separate rows for each sub ou a
given system is in.
Jeff Krueger
IT - Endpoint Design Services
Henry Ford Health System
[email protected]<mailto:[email protected]>
248.853.4466
________________________________
CONFIDENTIALITY NOTICE: This email contains information from the sender that
may be CONFIDENTIAL, LEGALLY PRIVILEGED, PROPRIETARY or otherwise protected
from disclosure. This email is intended for use only by the person or entity to
whom it is addressed. If you are not the intended recipient, any use,
disclosure, copying, distribution, printing, or any action taken in reliance on
the contents of this email, is strictly prohibited. If you received this email
in error, please contact the sending party by reply email, delete the email
from your computer system and shred any paper copies.
Note to Patients: There are a number of risks you should consider before using
e-mail to communicate with us. See our Privacy & Security page on
www.henryford.com<http://www.henryford.com> for more detailed information as
well as information concerning MyChart, our new patient portal. If you do not
believe that our policy gives you the privacy and security protection you need,
do not send e-mail or Internet communications to us.
The information transmitted is intended only for the person or entity to which
it is addressed
and may contain CONFIDENTIAL material. If you receive this material/information
in error,
please contact the sender and delete or destroy the material/information.
