开发者

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)


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


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


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.


If my unterstanding is correct :

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


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


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


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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜