I would like to know whether it is possible to use a SELECT statement in SQLite to merge the data from two rows into one, similar to how is suggested in the SQL Server forum below.

Consider the scenario below which is based on SQL Server (taken from http://forums.aspfree.com/microsoft-sql-server-14/merge-the-two-rows-in-one-row-245550.html)

Given there is a table

Emp

ID | Name |
1 | x |
1 | P |
2 | y |
2 | Q |
3 | W |

We want the resulting data from the select statement to output:

Emp_Data

Id | Name-1 | Name-2 |
1 | x | P |
2 | y | Q |
3 | w | |

The answer in the post suggests the following SQL as a possible solution:

SELECT
   a.ID,
   [Name-1] = ISNULL((
   SELECT TOP 1 Name
   FROM emp
   WHERE ID = a.ID),''),
   [Name-2] = ISNULL((
   SELECT TOP 1 b.Name
   FROM emp b
   WHERE b.ID = a.ID
       AND Name NOT IN(
           SELECT TOP 1 Name
           FROM emp
           WHERE ID = b.ID
       )),'')
FROM emp a
GROUP BY a.ID

Using SQLite is it possible to generate the columns [Name-1] & [Name-2] using nested SELECT statements like we can do above in SQL Server?

SELECT
   a.ID,
   COALESCE(a.Name,'') as "Name-1",
   COALESCE((SELECT b.Name FROM Emp b
             WHERE b.ID = a.ID
             AND b.rowid != a.rowid LIMIT 1),'') as "Name-2"
FROM emp a
GROUP BY a.ID

more