SQL Server Select statement

I have 6 columns that I need to string together.

col1 and 2 will always be populated with data
If column 3 is null columns 4-6 will be as well
If column 3 and 4 are populated and 5 is null, column 6 will be null as well

What I need is to have the data in one "view" that looks like this IF 1 3 and 5 all have data

col1  col2 / col3  col4 / col5 col6

But if col5 is null don't put the / or the nulls
col1  col2 / col3 col4

If col3-6 are null return only col1 col2
Larry Bristersr. DeveloperAsked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT ISNULL(Col1,'')+ISNULL(Col2,'')+ISNULL(Col3,'')+ISNULL(Col4,'')+ISNULL(Col5,'')+ISNULL(Col6,'')
FROM
0
 
Patrick MatthewsCommented:
SELECT (CASE
    WHEN col1 IS NOT NULL AND col3 IS NOT NULL AND col5 IS NOT NULL THEN
        col1 + ' ' + col2 + ' / ' + col3 + ' ' + col4 + ' / ' + col5 + ' ' + col6
    WHEN col5 IS NULL THEN col1 + ' ' + col2 + ' / ' + col3 + ' ' + col4
    ELSE col1 + ' ' + col2 END) AS Result
FROM SomeTable
0
 
dsackerContract ERP Admin/ConsultantCommented:
SELECT col1 + ' ' + col2 +
       case
         when col5 is not null then ' / ' + col3 + col4 + ' / ' + col5 + col6
         when col3 is not null then ' / ' + col3 + col4
         else ''
       end
FROM   myTable
0
 
Larry Bristersr. DeveloperAuthor Commented:
aneeshattingal:

You easily put me on the right track.  I added the spaces and '/' myself for a very quick resolution.  Thanks...you were first and points being awarded now.


Select  (ISNULL(dbo.vtblListingInput.AgentName,'')+ISNULL('  ' + dbo.vtblListingInput.AgentCell,'')
+ ISNULL(' /' + dbo.vtblListingInput.CoAgentName,'')
+ ISNULL('  ' + dbo.vtblListingInput.CoAgentCell,'')
+ ISNULL(' /' + dbo.vtblListingInput.CoAgent2Name,'')+ISNULL('  ' + dbo.vtblListingInput.CoAgent2Cell,'')) as AgentName

from .....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.