表：
field1 field2 field3
A001 B CA001
A001 C CA001
A001 D CA001
B001 E CB001
B001 F CB001
B001 G CB001
想要結果：
field1 field2 field3
A001 B,C,D CA001
B001 E,F,G CB001
uj5u.com熱心網友回復：
select po, [val]=stuff((select ',' + [custname] from test as a where a.po = b.po for xml path('')),1 ,1,'')from test as b
group by po
uj5u.com熱心網友回復：
--測驗資料
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([field1] nvarchar(24),[field2] nvarchar(21),[field3] nvarchar(25))
Insert #T
select N'A001',N'B',N'CA001' union all
select N'A001',N'C',N'CA001' union all
select N'A001',N'D',N'CA001' union all
select N'B001',N'E',N'CB001' union all
select N'B001',N'F',N'CB001' union all
select N'B001',N'G',N'CB001'
Go
--測驗資料結束
SELECT a.field1 ,
STUFF(( SELECT ',' + b.field2
FROM #T b
WHERE field1 = a.field1 AND field3 = a.field3
FOR
XML PATH('')
), 1, 1, '') AS field2 ,field3
FROM #T a
GROUP BY a.field1 ,field3
uj5u.com熱心網友回復：
USE tempdb
GO
IF OBJECT_ID('dbo.[t]') IS NOT NULL
DROP TABLE dbo.[t]
GO
CREATE TABLE dbo.[t](
[field1] NVARCHAR(20)
,[field2] NVARCHAR(10)
,[field3] NVARCHAR(20)
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[t] VALUES(N'A001',N'B',N'CA001')
INSERT INTO dbo.[t] VALUES(N'A001',N'C',N'CA001')
INSERT INTO dbo.[t] VALUES(N'A001',N'D',N'CA001')
INSERT INTO dbo.[t] VALUES(N'B001',N'E',N'CB001')
INSERT INTO dbo.[t] VALUES(N'B001',N'F',N'CB001')
INSERT INTO dbo.[t] VALUES(N'B001',N'G',N'CB001')
--SqlServer2016+以上寫法
SELECT [field1],STRING_AGG(field1,',') AS field2,[field3]
FROM t
GROUP BY [field1],[field3]
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/16159.html
標籤：疑難問題
上一篇：求一個酒店管理系統spring+boot整合+mybatis和themeleaf分開發
下一篇：如何把在線用戶踢出去，用陳述句