Duplicating Rows

Question

Hello

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?

Thanks

Answer 1

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

Answer 2

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   
   

Output:

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 3

Few references:

https://sqlzealots.com/2020/03/22/how-to-build-comma-separated-string-in-sql-server/

https://sqlzealots.com/2020/12/30/row-values-into-string-with-delimiter-in-sql-server/