SQL columns to string

Question

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)

What database system and what version of it?? String handling is quite vendor- and product-specific... — marc_s
– marc_s, Commented Jun 28, 2010 at 8:47
MySQL 2005 ?? Do you mean MySQL (that is numbered versions 4, 5, 6) or do you mean Microsoft SQL Server (which has a version 2005) ..... — marc_s
– marc_s, Commented Jun 28, 2010 at 9:55

Matt Mitchell · Accepted Answer · 2010-06-28 09:08:43Z

2

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

edited Jun 28, 2010 at 9:08

answered Jun 28, 2010 at 8:48

Matt Mitchell

42k35 gold badges122 silver badges186 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

erikkallen Over a year ago

Although you have to do CAST(1 AS nvarchar) + ' ' + CAST(2 AS nvarchar) + ... in SQL Server whenever you are concatenating values not typed as string.

krock · Accepted Answer · 2010-06-28 08:59:33Z

1

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

edited Jun 28, 2010 at 8:59

answered Jun 28, 2010 at 8:50

krock

29.7k13 gold badges81 silver badges86 bronze badges

2 Comments

nickik Over a year ago

How do I convert/cast NULL to '' in MS-SQL?

marc_s Over a year ago

@nickik: use the ISNULL(column, "") construct

Simon Hughes · Accepted Answer · 2010-06-28 08:50:09Z

0

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.

answered Jun 28, 2010 at 8:50

Simon Hughes

3,5943 gold badges27 silver badges46 bronze badges

Comments

Toto · Accepted Answer · 2010-06-28 08:50:42Z

0

If my unterstanding is correct :

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

answered Jun 28, 2010 at 8:50

Toto

91.7k63 gold badges97 silver badges135 bronze badges

Comments

Samiksha · Accepted Answer · 2010-06-28 08:53:01Z

0

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

answered Jun 28, 2010 at 8:53

Samiksha

6,2426 gold badges32 silver badges28 bronze badges

Comments

Mark Baker · Accepted Answer · 2010-06-28 08:54:53Z

0

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

answered Jun 28, 2010 at 8:54

Mark Baker

213k34 gold badges354 silver badges390 bronze badges

Comments

Moritz · Accepted Answer · 2010-06-28 08:57:09Z

0

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

answered Jun 28, 2010 at 8:57

Moritz

1991 gold badge1 silver badge7 bronze badges

Collectives™ on Stack Overflow

SQL columns to string

7 Answers 7

1 Comment

2 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

1 Comment

2 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related