I have a table:
Id Name
1 phucuong
2 ksks
3 na
I want output is:
phucuongksksna
how to write in sql?
I tried concat
, but it is not working.
CodePudding user response:
In SQL Server:
SELECT Name AS [text()] FROM YourTable FOR XML PATH ('')
In MySQL:
SELECT GROUP_CONCAT(Name SEPARATOR '') FROM YourTable
Another Solution SQL Server
SELECT STUFF
(
(
SELECT ',' CAST(RTRIM(LTRIM(g.Name)) AS VARCHAR(MAX))
FROM YourTable g,YourTable e
WHERE g.Id=e.Id
FOR XMl PATH('')
),1,1,''
)
CodePudding user response:
In both a SQL Server DB and a Postgres DB, we can use STRING_AGG
:
SELECT STRING_AGG(name,'') FROM yourtable;
In Oracle DB, we can use LISTAGG
:
SELECT LISTAGG(name) FROM yourtable; -- or LISTAGG(name,''), both will do
In MariaDB, the same query like in MYSQL will work:
SELECT GROUP_CONCAT(name SEPARATOR '') FROM yourtable;
In SQLite DB, it's very similar to the previous one:
SELECT GROUP_CONCAT(name,'') FROM yourtable;
Of course, there are also other possibilities/functions to achieve this, but as far as I know, these are the most efficient options.