Try something like this:
select REF, stuff((select distinct ', ' + [Field 1] from MyTable where REF = t.REF for xml path('')), 1, 2, '') as [Field 1], stuff((select distinct ', ' + [Field 2] from MyTable where REF = t.REF for xml path('')), 1, 2, '') as [Field 2] from MyTable t group by REF
2 additional answers
Sort by: Most helpful
Hi @Will Faulkner ,
For versions of SQL Server 2017 and later, the easiest way to merge strings is to use the function STRING_AGG (this function was introduced in SQL Server 2017), and for versions before SQL Server 2017, we generally use xml path or user-defined functions to merge strings.
The method of xml path has been provided by Viorel, the following is the method of using user-defined functions, please also check:
CREATE TABLE yourtable(REF int,Field1 varchar(15),Field2 varchar(15)) INSERT INTO yourtable VALUES(1,'a','x'),(1,'b','y'),(1,'c','z') CREATE FUNCTION fn_hb1(@REF VARCHAR(100)) RETURNS VARCHAR(8000) AS BEGIN DECLARE @Rst VARCHAR(8000) SET @Rst='' SELECT @Rst=@Rst+Field1+',' FROM yourtable WHERE REF=@REF SET @Rst=Left(@Rst,LEN(@RSt)-1) RETURN @Rst END GO CREATE FUNCTION fn_hb2(@REF VARCHAR(100)) RETURNS VARCHAR(8000) AS BEGIN DECLARE @Rst VARCHAR(8000) SET @Rst='' SELECT @Rst=@Rst+Field2+',' FROM yourtable WHERE REF=@REF SET @Rst=Left(@Rst,LEN(@RSt)-1) RETURN @Rst END GO SELECT * FROM yourtable SELECT REF,dbo.fn_hb1(REF) AS Field1,dbo.fn_hb2(REF) AS Field2 FROM yourtable GROUP BY REF; DROP TABLE yourtable DROP FUNCTION fn_hb1 DROP FUNCTION fn_hb2
If you have any question, please feel free to let me know.
If the answer is helpful, please click "Accept Answer" and upvote it.
I am using SQL Server 2014.
I want to report on three fields:
Ref, field 1, field 2
current output is:
REF Field 1 Field 2 1 a x 1 b y 1 c z
My desired output is just to have the distinct value of Ref - in this case, '1' , appear once and the other fields to appear on one line but with the values separated by a comma, so, desired output =
REF Field 1 Field 2 1 a,b,c x,y,z
please can you advise how I can achieve this?
What is your SQL Server version?
Sign in to comment
Thanks for the reply. I am using 2014. STRING_AGG is not an option.