例如原表如下
custid number
1 A
1 B
2 C
3 D
希望將custid通過逗號隔開，拼接到一個字串變數中回傳
目前寫好的sql是
declare @custids nvarchar(255)
set @custids = ''
select @custids = @custids+',' cast(custid as nvarchar) from tab
select @custids
但是這樣查詢的結果是 ',1,1,2,3' ，期望的結果是',1,2,3' ，除下面查詢陳述句外是否有其它陳述句實作需求
select @custids = @custids+',' cast(t.custid as nvarchar) from ( select distinct custid from tab) t
uj5u.com熱心網友回復：
SELECT DISTINCT ','+CAST(custid AS VARCHAR(5)) FROM tab FOR XML PATH('')uj5u.com熱心網友回復：
USE tempdb
GO
IF OBJECT_ID('dbo.[tab]') IS NOT NULL
DROP TABLE dbo.[tab]
GO
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(N'2',N'C')
INSERT INTO dbo.[tab] VALUES(N'3',N'D')
------- 以上為測驗表
declare @custids nvarchar(255)
set @custids = ''
select @custids = @custids+','+cast(custid as NVARCHAR(20)) from tab GROUP BY custid
select @custids
GO
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/277929.html
標籤：疑難問題
上一篇：全文檢索時查不出右匹配的資料