[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 175
  • Last Modified:

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
0
lrbrister
Asked:
lrbrister
1 Solution
 
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
 
lrbristerAuthor 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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now