Help with combining columns MS Sql Select statement
Hello Experts,
I have the Select query below, the issue I have is that I want to combine C.JBCAFirstName and C.JBACAName into one output - JBCName, however as the JBACAFirstName was a new addition to the table, there will often be senarios whereby JBACAFirstName is NULL, so in that instance I want to show just the value of JBACAName, rather than -
NULL Garry etc
Possible? Please help - thanks
SelectAP.JBAPCoveringLetter,CONVERT(CHAR(11),AP.JBAPDate,106) AS JBAPDate,C.JBCAFirstName,C.JBACAName,AD.JBATitle,AD.JBAReference,E.JBEName,S.JBSURLShortFrom dbo.JBApplication APinner join dbo.JBAdvert AD on AP.JBAPAdvertID = AD.JBAIDinner join dbo.JBEmployee E on AP.JBAPEmployeeID = E.JBEIDinner join dbo.JBACandidate C on AP.JBAPCandidateID = C.JBACAIDinner join dbo.JBSite S on AP.JBAPSiteID = S.JBSSiteIDWhere AP.JBAPID = 13200
Microsoft SQL Server 2008Microsoft SQL Server 2005
Last Comment
garethtnash
8/22/2022 - Mon
hyphenpipe
Like this.
SelectAP.JBAPCoveringLetter,CONVERT(CHAR(11),AP.JBAPDate,106) AS JBAPDate,cast C.JBCAFirstName when null then C.JBACAName else C.JBCAFirstName + ' ' + C.JBACAName end as JNCname,AD.JBATitle,AD.JBAReference,E.JBEName,S.JBSURLShortFrom dbo.JBApplication APinner join dbo.JBAdvert AD on AP.JBAPAdvertID = AD.JBAIDinner join dbo.JBEmployee E on AP.JBAPEmployeeID = E.JBEIDinner join dbo.JBACandidate C on AP.JBAPCandidateID = C.JBACAIDinner join dbo.JBSite S on AP.JBAPSiteID = S.JBSSiteIDWhere AP.JBAPID = 13200
SelectAP.JBAPCoveringLetter,CONVERT(CHAR(11),AP.JBAPDate,106) AS JBAPDate,case C.JBCAFirstName when null then C.JBACAName else C.JBCAFirstName + ' ' + C.JBACAName end as JNCname,AD.JBATitle,AD.JBAReference,E.JBEName,S.JBSURLShortFrom dbo.JBApplication APinner join dbo.JBAdvert AD on AP.JBAPAdvertID = AD.JBAIDinner join dbo.JBEmployee E on AP.JBAPEmployeeID = E.JBEIDinner join dbo.JBACandidate C on AP.JBAPCandidateID = C.JBACAIDinner join dbo.JBSite S on AP.JBAPSiteID = S.JBSSiteIDWhere AP.JBAPID = 13200
SelectAP.JBAPCoveringLetter,CONVERT(CHAR(11),AP.JBAPDate,106) AS JBAPDate,IsNull(C.JBCAFirstName, "") + ' ' + C.JBACAName AS Name,AD.JBATitle,AD.JBAReference,E.JBEName,S.JBSURLShortFrom dbo.JBApplication APinner join dbo.JBAdvert AD on AP.JBAPAdvertID = AD.JBAIDinner join dbo.JBEmployee E on AP.JBAPEmployeeID = E.JBEIDinner join dbo.JBACandidate C on AP.JBAPCandidateID = C.JBACAIDinner join dbo.JBSite S on AP.JBAPSiteID = S.JBSSiteIDWhere AP.JBAPID = 13200
Open in new window