multiple case statements using multiple columns

Question

I have two columns in a table , say "colA", "colB" and "colC" need to be computed.

Case 1: Here, colC is a combination of colA and colB with delimiter as space.

For example , if colA = 'abc' and colB = 'xyz' then colC = 'abc xyz'.

Case 2: Here , if either of the colums (colA , colB) is null then colC should contain the non-null value among colA and colB.

For example , if colA = null and colB = 'xyz' then colC = 'xyz'. if colA = 'abc' and colB = null then colC = 'abc'.

How can I achieve this in a select statement using CASE or some other best approach?

Answer 1

CREATE TABLE #three
(
  colA varchar(32),
  colB varchar(32)
);

INSERT #three(colA, colB) VALUES
  ('abc', 'xyz'),
  ('def', NULL),
  (NULL,  'ghi'),
  (NULL,  NULL);

SELECT ColA, ColB, 
  ColC = LTRIM(COALESCE(colA, '') + COALESCE(' '+colB,''))
FROM #three;

DROP TABLE #three;

Results:

ColA  ColB  ColC
----  ----  -------
abc   xyz   abc xyz
def   NULL  def
NULL  ghi   ghi
NULL  NULL