CODEWITHSUNDEEP
sql
nickik
nickik

Reputation: 5881

SQL columns to string

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)

Upvotes: 2

Views: 1358

Answers (7)

Matt Mitchell
Matt Mitchell

Reputation: 41823

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

Upvotes: 2

krock
krock

Reputation: 29619

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

Upvotes: 1

Moritz
Moritz

Reputation: 199

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

Upvotes: 0

Mark Baker
Mark Baker

Reputation: 212412

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

Upvotes: 0

Samiksha
Samiksha

Reputation: 6182

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

Upvotes: 0

Toto
Toto

Reputation: 91385

If my unterstanding is correct :

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

Upvotes: 0

Simon Hughes
Simon Hughes

Reputation: 3574

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.

Upvotes: 0

Related Questions