asked on 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
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
Select
AP.JBAPCoveringLetter,
CONVERT(CHAR(11),AP.JBAPDate,106) AS JBAPDate,
C.JBCAFirstName,
C.JBACAName,
AD.JBATitle,
AD.JBAReference,
E.JBEName,
S.JBSURLShort
From dbo.JBApplication AP
inner join dbo.JBAdvert AD on AP.JBAPAdvertID = AD.JBAID
inner join dbo.JBEmployee E on AP.JBAPEmployeeID = E.JBEID
inner join dbo.JBACandidate C on AP.JBAPCandidateID = C.JBACAID
inner join dbo.JBSite S on AP.JBAPSiteID = S.JBSSiteID
Where AP.JBAPID = 13200
Microsoft SQL Server 2008Microsoft SQL Server 2005
Last Comment
garethtnash
Sorry meant case.
Select
AP.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.JBSURLShort
From dbo.JBApplication AP
inner join dbo.JBAdvert AD on AP.JBAPAdvertID = AD.JBAID
inner join dbo.JBEmployee E on AP.JBAPEmployeeID = E.JBEID
inner join dbo.JBACandidate C on AP.JBAPCandidateID = C.JBACAID
inner join dbo.JBSite S on AP.JBAPSiteID = S.JBSSiteID
Where AP.JBAPID = 13200
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
try this,
Select
AP.JBAPCoveringLetter,
CONVERT(CHAR(11),AP.JBAPDate,106) AS JBAPDate,
IsNull(C.JBCAFirstName, "") + ' ' + C.JBACAName AS Name,
AD.JBATitle,
AD.JBAReference,
E.JBEName,
S.JBSURLShort
From dbo.JBApplication AP
inner join dbo.JBAdvert AD on AP.JBAPAdvertID = AD.JBAID
inner join dbo.JBEmployee E on AP.JBAPEmployeeID = E.JBEID
inner join dbo.JBACandidate C on AP.JBAPCandidateID = C.JBACAID
inner join dbo.JBSite S on AP.JBAPSiteID = S.JBSSiteID
Where AP.JBAPID = 13200
ASKER
Fantastic Thank you
Open in new window