Selecting multiple columns into a single variable.

[mike55]

Hi

 

Need to select multiple columns in a row into a single variable, example - persons address: line 1, line 2, line3, town, county, country as myAddress.

 

Any suggestions?

 

Mike55.

[mike55]

Found a solution, not sure if it is the best solution, would appreciate it if some one could say it is or it isn't the best solution.

 

SELECT ( column1 + ', ' + column2 + ', ' + column3) as Exp1
FROM Table1

[IxiRancid]

What are you doing with the result then? If this is a VB or ASP.NET app then it's easy to read results into a string. However if you want to have a direct Expr in SQL statement then your way is a possibility. But perhaprs something could be done with Aliases?

[penfold69]

SELECT CONCAT(column1, ", ", column2, ", ", ....) as Exp1
FROM Table1

 

This will NOT throw an error if one of the fields happens to be numerical (probably not in your case, but you never know!)

[Nerseus]

@mike55:

If you don't need the columns separated out for any reasons, I'd do it just the way you are, using + or whatever your DB uses for concatenation. You may want/need to wrap each column in case the values are null. In most databases, if you try to concatenate 2 strings and one is null, the result is null.

 

In SQL Server it would look like this:

SELECT IsNull(column1, '') + ', ' + IsNull(column2, '') + ', ' + IsNull(column3, '') as Exp1
FROM Table1

 

You can also use COALESCE (maybe with 2 L's?) instead of IsNull if you prefer.

 

-ner