How to Use Select Distinct and Concat in the Same SQL Statement

How to use SELECT DISTINCT and CONCAT in the same SQL statement

Distinct goes against the entire row of ALL columns, not just the name portion... So if the appointments are on different date/times, locations, etc, they will all come out. If all you want to show is the NAME portion, strip the rest of the other content. Query the available appointments AFTER a person has been chosen.

How to make a DISTINCT CONCAT statement?

One option is to use GROUP BY:

SELECT CONCAT(CONCAT(EMPLOYEEFNAME, ' '), EMPLOYEELNAME) AS "Employee",
       JOBTITLE AS "Job Title" 
FROM Employee
GROUP BY CONCAT(CONCAT(EMPLOYEEFNAME, ' '), EMPLOYEELNAME),
         JOBTITLE
ORDER BY "Employee"

Another option, if you really want to use DISTINCT, would be to subquery your current query:

SELECT DISTINCT t.Employee,
                t."Job Title"
FROM
(
    SELECT CONCAT(CONCAT(EMPLOYEEFNAME, ' '), EMPLOYEELNAME) AS "Employee",
           JOBTITLE AS "Job Title" 
    FROM Employee
) t

Combine CONCAT and DISTINCT mysql

Need to update your query like this

SELECT DISTINCT CONCAT('prefix_', name) nameFROM `test`

Note
When only one expression is provided in the DISTINCT clause, the query will return the unique values for that expression.
When more than one expression is provided in the DISTINCT clause, the query will retrieve unique combinations for the expressions listed.
In MySQL, the DISTINCT clause doesn't ignore NULL values. So when using the DISTINCT clause in your SQL statement, your result set will include NULL as a distinct value.

OR

you can use group by

select CONCAT("prefix_", name) 
from Test 
group by name 

for more information
https://www.w3schools.com/sql/sql_distinct.asp

SQL Select All Where the Concat is Distinct

DISTINCT is used on the column selection. There are a couple ways to do this:

SELECT DISTINCT CONCAT(P_NUMBER,'-',A_NUMBER) AS CNUMBER FROM APPROVED WHERE 
AND (STATUS = 'ACTIVE' OR STATUS = 'CLOSED' OR STATUS = 'CLOSING' OR STATUS =     'PENDING')

that should do almost the same thing as

SELECT * FROM APPROVED WHERE 
AND (STATUS = 'ACTIVE' OR STATUS = 'CLOSED' OR STATUS = 'CLOSING' OR STATUS =     'PENDING')
GROUP BY CONCAT(P_NUMBER,'-',A_NUMBER)

the 2nd doesn't include the concatenation in the results, and the first one ONLY includes the concatenation. You'll need to specify all the columns to return.

Concatenate distinct values in a group by

What Laurenz meant with string_agg() is the following

SELECT
  group, 
  STRING_AGG(Provider,',') as ProviderList
FROM data 
GROUP BY 1

Optionally you could also use:

STRING_AGG(provider,',' order by Provider)

SQL Concatenate Distinct Values

I much prefer stuff() rather than substring() for removing the separating character. What you require, though, is select distinct or group by in the subquery:

stuff((SELECT ',' +  ST1.ProductNo  AS [text()]
       FROM uAccountProductInfo ST1
       WHERE ST1.version_num = ST2.version_num AND
             ST1.Account_No = ST2.Account_No  
       GROUP BY ST1.ProductNo
       ORDER BY MIN(ST1.RowNo) 
       FOR XML PATH ('')
      ), 1, 1, '')

Your additional subquery is superfluous. In fact, it is misleading because you are using SELECT DISTINCT and expect it to return one row per ProductNo -- even when multiple rows exist with different values in the other columns.

Note that the ordering is unclear. This bases it on the minimum RowNo.

ORA-00979 : select distinct(concat column) not a group by expression

You may repeat the entire expression in the GROUP BY clause as it appears in the SELECT:

SELECT DISTINCT colA || SUBSTR(colB, 1, 2) AS colA
FROM myTable
WHERE colC = '5678'
GROUP BY colA || SUBSTR(colB, 1, 2);

Oracle does not supporting grouping using aliases, or using 1, 2, etc. positional parameters. And note that DISTINCT is not a SQL function.

how to select distinct and concatenate in sql

You can use for xml path to concatenate the values:

select distinct name + ', ' as [text()]
from @t
for xml path('')

-->
Elephant, Lion, Rhino, Tiger, Wolf, 

Chop of the last 2 bytes if you don't like trailing ,'s.

Not sure why you can't use T-SQL, you can use this in combination with ExecuteScalar() just fine.

Sample data:

declare @t table (name varchar(max), id int)
insert into @t 
select 'Tiger', 50
union all select 'Wolf', 4
union all select 'Tiger', 53
union all select 'Lion', 55
union all select 'Elephant', 54
union all select 'Rhino', 52
union all select 'Lion', 5

---

Related Topics