SQL columns to string

Question

Hello all,

I have a SQL query like this (pseudo code)

Select ID, TEXT, 1, 2, 3, 4, 5, 6, 7 from blabla

but what I need is a query with 3 columns

Select ID, TEXT, STRING(1, 2, 3, 4, 5, 6, 7) from blabla

can I do this with just SQL?

EDIT:

Extra question: It doesn't work if 1-7 return NULL or nothing. How can i convert NULL to an empty string? (MySQL 2005)

Answer 1

In T-SQL / MS-SQL:

# With Space
SELECT ID, TEXT, 
  (1 + ' ' + 2 + ' ' + 3 + ' ' + 4 ' ' + 5 + ' ' + 6 + ' ' + 7) as Merged 
FROM blabla

# Without Space
SELECT ID, TEXT, 
  (1 + 2 + 3 + 4 + 5 + 6 + 7) as Merged 
FROM blabla

In My-SQL:

# With Space
SELECT ID, TEXT, 
  CONCAT_WS(' ', 1, 2, 3 , 4, 5, 6, 7) as Merged 
FROM blabla

# Without Space
SELECT ID, TEXT, 
  CONCAT(1, 2, 3 , 4, 5, 6, 7) as Merged 
FROM blabla

In PL-SQL/Oracle:

# With Space
SELECT ID, TEXT, 
  1 || ' ' || 2 || ' ' || 3 || ' ' || 4 || ' ' || 5 || ' ' || 6 || ' ' || 7 as Merged 
FROM blabla

# Without Space
SELECT ID, TEXT, 
  1 || 2 || 3 || 4 || 5 || 6 || 7 as Merged 
FROM blabla

Answer 2

Just us the + character to concatenate strings, assuming your 1-7 are string fields:

Select ID, TEXT, 1 + ' ' + 2 + ' ' + 3 + ' ' +
4 + ' ' + 5 + ' ' + 6 + ' ' + 7 as YourFieldName from blabla

I've included spaces between the fields, however you could also provide a comma or other delimeter.

Answer 3

In t-sql:

Select ID, TEXT, 1 + 2 + 3 + 4 + 5 + 6 + 7 from blabla

In PL-SQL:

Select ID, TEXT, 1 || 2 || 3 || 4 || 5 || 6 || 7 from blabla

In MySQL:

Select ID, TEXT, CONCAT(1, 2, 3, 4, 5, 6, 7) from blabla

convert/casts may be required depending on the data types of columns 1 - 7

Answer 4

If my unterstanding is correct :

Select ID, TEXT, '1, 2, 3, 4, 5, 6, 7' from blabla;

Answer 5

Select ID,TEXT,'1','2','3','4','5','6','7' from blabla;

Answer 6

Select ID, 
       TEXT, 
       CONCAT_WS(',', 1, 2, 3, 4, 5, 6, 7) as Concatenated
  from blabla 

or

Select ID, 
       TEXT, 
       CONCAT(1, ',', 2, ',', 3, ',', 4, ',', 5, ',', 6, ',', 7) as Concatenated
  from blabla 

in MySQL

Select ID, 
       TEXT, 
       1 || ',' || 2 || ',' || 3 || ',' || 4 || ',' || 5 || ',' || 6 || ',' || 7) as Concatenated
  from blabla 

in Oracle

but depends on your flavour of database

Answer 7

i think its not possible to use a comma instead of plus