SQLite Select data from multiple rows returned as

问题:

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?

回答1:

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

回答2:

try this:::

select id, group_concat(name) from emp group by id;

;)

回答3:

Doug's solution didn't work for me. The code below, however, did work for me but it's very slow...

SELECT 
   a.ID, 
   a.Name AS Name1,
   (SELECT b.Name FROM Emp b 
             WHERE b.ID = a.ID
             AND b.Name != a.Name LIMIT 1) AS Name2
FROM emp a 
GROUP BY a.ID