One table has different columns for each column I want to do a group by function. I get unique values for each column in the table.
Origin Table data:
+----+--------------+--------------+
| id | customername | amazonnumber |
+----+--------------+--------------+
| 1 | Mark | 1122 |
| 2 | Pieter | 1122 |
| 3 | Jean | 1122 |
| 4 | Jean | 1122 |
| 5 | Janis | 1123 |
| 6 | Janis | 1123 |
+----+--------------+--------------+
At this moment i do this:
SELECT customername FROM tablegroup group by customername;
SELECT amazonnumber FROM tablegroup group by amazonnumber;
This is great i get for each column the unique value.
I want to combine them so i get all the data vertical in columns. I have tried this but, this give not the right results:
SELECT * FROM
(SELECT customername FROM tablegroup group by customername) b
CROSS JOIN
(SELECT amazonnumber FROM tablegroup group by amazonnumber) c;
This is the wrong result:
+---------------+--------------+
| customername | amazonnumber |
+---------------+--------------+
| Mark | 11222 |
| Pieter | 11222 |
| Jean | 11222 |
| Janis | 11222 |
+---------------+--------------+
End Result:
+---------------+--------------+
| customername | amazonnumber |
+---------------+--------------+
| Mark | 11222 |
| Pieter | 11223 |
| Jean | |
| Janis | |
+---------------+--------------+
2 Answers 2
For MySQL 8+:
WITH RECURSIVE
cte1 AS ( SELECT customername, ROW_NUMBER() OVER () rn
FROM tablegroup
GROUP BY customername
),
cte2 AS ( SELECT amazonnumber, ROW_NUMBER() OVER () rn
FROM tablegroup
GROUP BY amazonnumber
),
cte3 AS ( SELECT 1 num
UNION ALL
SELECT num+1
FROM cte3
WHERE num < ( SELECT GREATEST(cnt1, cnt2)
FROM ( SELECT COUNT(*) cnt1
FROM cte1
),
( SELECT COUNT(*) cnt2
FROM cte2
)
)
)
SELECT cte1.customername, cte2.amazonnumber
FROM cte3
LEFT JOIN cte1 ON cte3.num = cte1.rn
LEFT JOIN cte2 ON cte3.num = cte2.rn
Another MySQL 8+ option:
WITH
ranked AS
(
SELECT
customername,
amazonnumber,
DENSE_RANK() OVER (ORDER BY customername ASC) AS cust_rn,
DENSE_RANK() OVER (ORDER BY amazonnumber ASC) AS amaz_rn
FROM
tablegroup
),
allrankings AS
(
SELECT DISTINCT
x.rn
FROM
ranked,
LATERAL
(
SELECT ranked.cust_rn AS rn
UNION ALL
SELECT ranked.amaz_rn
) AS x
)
SELECT
c.customername,
a.amazonnumber
FROM
allrankings AS r
LEFT JOIN (SELECT DISTINCT customername, cust_rn FROM ranked) AS c ON r.rn = c.cust_rn
LEFT JOIN (SELECT DISTINCT amazonnumber, amaz_rn FROM ranked) AS a ON r.rn = a.amaz_rn
;
The ranked CTE generates unique rankings for customername and for amazonnumber. It transforms your data sample like this:
+--------------+--------------+---------+---------+
| customername | amazonnumber | cust_rn | amaz_rn |
+--------------+--------------+---------+---------+
| Mark | 1122 | 3 | 1 |
| Pieter | 1122 | 4 | 1 |
| Jean | 1122 | 2 | 1 |
| Jean | 1122 | 2 | 1 |
| Janis | 1123 | 1 | 2 |
| Janis | 1123 | 1 | 2 |
+--------------+--------------+---------+---------+
The allrankings CTE derives all the unique rankings from both ranking columns of ranked:
+----+
| rn |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
+----+
Then all the unique combinations of (customername, cust_rn):
+--------------+---------+
| customername | cust_rn |
+--------------+---------+
| Janis | 1 |
| Jean | 2 |
| Mark | 3 |
| Pieter | 4 |
+--------------+---------+
and all the unique combinations of (amazonnumber, amaz_rn):
+--------------+---------+
| amazonnumber | amaz_rn |
+--------------+---------+
| 1122 | 1 |
| 1123 | 2 |
+--------------+---------+
are outer-joined against the allrankings set to produce the following output:
+--------------+--------------+
| customername | amazonnumber |
+--------------+--------------+
| Janis | 1122 |
| Jean | 1123 |
| Mark | |
| Pieter | |
+--------------+--------------+
Admittedly, it slightly differs from your expected output because of the order of values in customername. I believe this to be a minor issue because there is no meaningful relationship between customername and amazonnumber in either this or your output anyway.
This solution can be tested using this live demo at dbfiddle logodb<>fiddle.uk.
Other useful links:
create table tablegroup. Show some sample data as its unclear as to who the end result is a grouping. Avoid using 'wanna' as its not a word and just makes you sound like an infant.