SQL - Combine multiple fields from a table into a single column

ttist25
ttist25 used Ask the Experts™
on
Hello,

I'm trying to concatenate the values in multiple columns into a string in another column.

Here is some SQL:
CREATE TABLE #tmpExample ( 
    column1 varchar(255),
    column2 varchar(255),
    column3 varchar(255),
	column4 varchar(255)
);
INSERT INTO #tmpExample  
VALUES 
('1', 'A', 'B', 'C'),
('2', 'D', 'E', 'F'),
('3', 'G', 'H', 'I'),
('4', '', '4', 'ME');

ALTER TABLE #tmpExample
ADD columns2_4 varchar(255)

Open in new window


Given that data I would like to write a statement that results in the following in "columns2_4":
ABC
DEF
GHI
PIZZA4ME

I'm hungry right now and so I'm thinking ISNULL(COLUMN2, 'PIZZA').

Actually any NULL in column 2,3, or 4 should return pizza (I'm REALLY hungry).  

TIA!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Note that Row 4, column 2, is not NULL - it is an empty string, which is a different thing, and changes the code. If you want to handle both empty strings and NULLs, then add this to the end of your code:

UPDATE #tmpExample
SET columns2_4 = CONCAT(ISNULL(NULLIF(column2, ''), 'PIZZA'), ISNULL(NULLIF(column3, ''), 'PIZZA'), ISNULL(NULLIF(column4, ''), 'PIZZA'))

SELECT *
FROM #tmpExample

Open in new window

Author

Commented:
Great thanks Brendt!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial