Duplicating Rows

Will Faulkner 201 Reputation points
2021-07-01T14:46:41.17+00:00

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,544 questions
{count} votes

Accepted answer
  1. Viorel 111.5K Reputation points
    2021-07-01T15:00:57.247+00:00

    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 person found this answer helpful.

2 additional answers

Sort by: Most helpful
  1. EchoLiu-MSFT 14,571 Reputation points
    2021-07-02T01:50:10.273+00:00

    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:
    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.


  2. SQLZealots 276 Reputation points
    2021-07-02T03:00:10.887+00:00