Avatar of garethtnash
garethtnash
Flag for United Kingdom of Great Britain and Northern Ireland 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
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

Open in new window

Microsoft SQL Server 2008Microsoft SQL Server 2005

Avatar of undefined
Last Comment
garethtnash

8/22/2022 - Mon
hyphenpipe

Like this.
Select
AP.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.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

Open in new window

hyphenpipe

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

Open in new window

ASKER CERTIFIED SOLUTION
hyphenpipe

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
krunal_shah

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

Open in new window

Your help has saved me hundreds of hours of internet surfing.
fblack61
garethtnash

ASKER
Fantastic Thank you