SQLite Select data from multiple rows returned as

2019-02-05 13:26发布

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?

3条回答
太酷不给撩
2楼-- · 2019-02-05 13:47

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
查看更多
疯言疯语
3楼-- · 2019-02-05 13:50

try this:::

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

;)

查看更多
爱情/是我丢掉的垃圾
4楼-- · 2019-02-05 13:55
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
查看更多
登录 后发表回答