Scott Chu Scott Chu - 6 months ago 23
SQL Question

Show column value twice in MySql Select

If I have a MySql table with values

col1
=====
1
1
1
5
5
5
5
7
7


I want to get all distinct values of col1 (i.e. 1,5,) and get output with one SELECT statement as follows:

1~1
5~5
7~7


How can I do this?

Answer

Here's the query:

Use separator ~ in GROUP_CONCAT function.

Query #1 use case scenario: If you want to separate the col1 values by tide (~) only if the corresponding value has identical values across the table.

Query #1:

SELECT 
 GROUP_CONCAT(col1 SEPARATOR '~') output
FROM scotttable
GROUP BY col1;

SQL FIDDLE DEMO

Note: It will work for any number of identical values.


Query #2 use case scenario: Only if you want the distinct col1 values no matter if they have corresponding identical value across the table exists or not.

Query #2:

SELECT 
 CONCAT(col1,'~',col1) output
FROM scotttable
GROUP BY col1;

FIDDLE OF THIS QUERY