Field1 field2 field3
A001 CA001 B
A001 CA001 C
A001 D CA001
B001 E CB001
B001 F CB001
B001 G CB001
Want results:
Field1 field2 field3
A001 B, C, D CA001
B001 E, F, G CB001
CodePudding user response:
Select the Po, val=stuff ((select ', '+ [custname] from the test as a where Amy polumbo o=p. o for XML path ('')), 1, 1, ' ')From the test as b
Group by Po
CodePudding user response:
The test data-If not object_id (N 'Tempdb for.. # T ') is null
Drop table # T
Go
The 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', 'D' N, 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
- the end of the test data
The SELECT a.f ield1,
STUFF ((SELECT the ', '+ b. ield2
The FROM # T b
WHERE field1=a.f ield1 AND field3=a.f ield3
FOR
XML PATH (' ')
), 1, 1, ' ') AS field2, field3
The FROM # T a
GROUP BY a.f ield1, field3
CodePudding user response:
USE tempdb for
GO
IF OBJECT_ID (' dbo. [t]) IS NOT NULL
DROP TABLE dbo. [t]
GO
The CREATE TABLE dbo. [t] (
[field1] NVARCHAR (20)
, field2 NVARCHAR (10)
, field3 NVARCHAR (20)
)
GO
SET NOCOUNT ON
INSERT INTO dbo. [t] VALUES (' A001 'N, 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')
- written above SqlServer2016 +
SELECT [field1], STRING_AGG (field1, ', ') AS field2, [field3]
The FROM t
GROUP BY [field1], [field3]