CODEWITHSUNDEEP
sql-serverdistinct
Muhammad Nasir
Muhammad Nasir

Reputation: 2204

SQL Server Selecting DISTINCT record to string variable

I want to select all distinct name from person table to VARCHAR variable . I have write a query that returns all name as follow.

DECLARE @pName as VARCHAR(MAX)
SET @pName=''

SELECT @pName += RTRIM(FullName) + ',' 
FROM Persons 

SELECT @pName

When I try to select distinct on FullName, SQL Server throws an exception.

DECLARE @pName as VARCHAR(MAX)
SET @pName=''

SELECT DISTINCT FullName, @pName += RTRIM(FullName) + ',' 
FROM Persons 

SELECT @pName

Msg 141, Level 15, State 1, Line 3
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

Distinct on variable name return only first Name

SELECT DISTINCT @pName += RTRIM(FullName) +
FROM Persons 

SELECT @pName

How can I select distinct name to string variable from SELECT statement?

Thanks in advance

Upvotes: 1

Views: 5573

Answers (2)

Ahmed Saeed
Ahmed Saeed

Reputation: 851

DECLARE @pName as VARCHAR(MAX)
with cte as(
SELECT DISTINCT FullName FROM Persons
) 
Select @pName = coalesce(@pName + ', ', '') + FullName 
from cte

select @pName

Upvotes: 1

Pintu Kawar
Pintu Kawar

Reputation: 2156

You can get the same distinct result without using the variable also.

SELECT STUFF((SELECT distinct ',' + FullName FROM Persons
              FOR xml path ('')
             ), 1, 1, '')

Using variable - Add a group By

DECLARE @pName as VARCHAR(MAX)
set @pName=''
SELECT    @pName+= RTRIM(FullName) + ',' FROM Persons Group By FullName
select @pName

Upvotes: 1

Related Questions