Grouping repeated numbers sqlite table-CodePudding

I have this table and I need the query to show the numbers repeated more than 2 times in which draw.

drawnumber|n1|n2|n3|
-------------------------
2000      |3 |20|18|
-------------------------------
2001      |21|18|11|
-------------------------------
2002      |11|20|18|
-------------------------------
2003      |3 |20|18|

I expect this result

numbers|quantity |Draw              |
--------------------------------------------------
3       |  2     | 2000, 2003
-------------------------------------------------|
20      |  3     | 2000, 2002, 2003              |
-------------------------------------------------|
18      |  3     | 2000, 2002, 2003      |
-------------------------------------------------|
11      |  2     | 2001, 2002                    |
-------------------------------------------------|

I'm doing this:

select NUMBERS,quantity, Draw FROM(
SELECT (" "||n1||"-"||n2||"-"||n3||)AS 'NUMBERS', COUNT(*) quantity
GROUP_CONCAT(" "||drawnumber)AS Draw from MyTbl
GROUP BY NUMBERS HAVING count(*) > 1

But it doesn't work.

Thanks in advance.

CodePudding user response：

You can first transpose your data with a cte and union, and then use group by:

with t(d, n) as (
    select drawnumber, n1 from draws
    union all
    select drawnumber, n2 from draws
    union all
    select drawnumber, n3 from draws
)
select n, count(*) quantity, group_concat(d) from t group by n having quantity > 1;