How to manage the assignment result of distinct SQL-CodePudding

For example, the original table below
Custid number
1 A
1 B
2 C
3 D
Hope will custid by commas, joining together into a string variable return

Now write good SQL is
Declare @ custids nvarchar (255)
The set @ custids='
Select @ custids=@ custids + ', 'cast (custid as nvarchar) from the TAB
Select @ custids

But that the results of a query is a ', 1,1,2,3 ', the desired result is a ', 1, 2, 3, in addition to the below query whether there are other statements implementation requirements
Select @ custids=@ custids + ', 'cast (tc ustid as nvarchar) from (select distinct custid from TAB) t

CodePudding user response:

SELECT DISTINCT ', '+ CAST (custid AS VARCHAR (5)) FROM the TAB FOR XML PATH ('')

CodePudding user response:

 USE tempdb for 
GO 
IF OBJECT_ID (' dbo. [TAB]) IS NOT NULL 
DROP TABLE dbo. [TAB] 
GO 
The CREATE TABLE dbo. [TAB] (
[custid] INT 
, [number] NVARCHAR (10) 
) 
GO 
SET NOCOUNT ON 
INSERT INTO dbo. [TAB] VALUES (N '1', N 'A') 
INSERT INTO dbo. [TAB] VALUES (N '1', N 'B') 
INSERT INTO dbo. [TAB] VALUES (' 2 'N, N' C ') 
INSERT INTO dbo. [TAB] VALUES (' 3 'N, N' D ') 
-- -- -- -- -- -- -- to test the above table 

Declare @ custids nvarchar (255) 
The set @ custids='
Select @ custids=@ custids + ', '+ cast (custid as NVARCHAR (20) from the TAB GROUP BY custid 
Select @ custids 
GO