Link to home
Start Free TrialLog in
Avatar of garethtnash
garethtnashFlag 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

Avatar of hyphenpipe
hyphenpipe
Flag of United States of America image

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

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
Avatar of hyphenpipe
hyphenpipe
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of krunal_shah
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

Avatar of garethtnash

ASKER

Fantastic Thank you