question

WillFaulkner-2982 avatar image
0 Votes"
WillFaulkner-2982 asked WillFaulkner-2982 commented

Duplicating Rows

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





sql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@WillFaulkner-2982,

What is your SQL Server version?

 SELECT @@VERSION;
0 Votes 0 ·

Thanks for the reply. I am using 2014. STRING_AGG is not an option.

0 Votes 0 ·
Viorel-1 avatar image
1 Vote"
Viorel-1 answered WillFaulkner-2982 commented

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


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thanks - will give that a go and will report back - cheers for the speedy response

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @WillFaulkner-2982,

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:
111086-image.png


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.



image.png (1.4 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you Echo. Sadly I'm stuck with 2014, not 2017, so STRING_AGG is off the table.

0 Votes 0 ·

I saw in your post that your SQL Server version is 2014, so just by the way for STRING_AGG, in case you accidentally use it.

0 Votes 0 ·
SQLZealots avatar image
0 Votes"
SQLZealots answered WillFaulkner-2982 commented
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

helpful thanks

0 Votes 0 ·