How to Concatenate two columns with duplicated values

Victor Emil Søe Rasmussen - DSV 0 Reputation points
2023-03-31T14:49:10.9033333+00:00

Hi, I have a small problem that I can't really figure out how to solve.
I don't really know what to call this problem, but it's understandable below

I have a table that looks like this (as a CSV file with comma as delimiter):
ParentRecID, Deploy Issue
ID1, Process Issue
ID1, Timing Issue
ID2, Process Issue
ID3, Process Issue
ID4, Timing Issue
ID4, Technical Issue

And what I want it to look like, is like this:
ParentRecID, Deploy Issue
ID1, Process Issue | Timing Issue
ID2, Process Issue
ID3, Process Issue
ID4, Timing Issue | Technical Issue

How would I do this in Power query?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,899 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Yitzhak Khabinsky 25,206 Reputation points
    2023-03-31T15:03:42.51+00:00

    Hi @Victor Emil Søe Rasmussen,

    Please try the following solution.

    It will work starting from SQL Server 2017 onwards.

    -- DDL and sample data population, start
    DECLARE @tbl TABLE (ParentRecID VARCHAR(5), DeployIssue VARCHAR(30));
    INSERT @tbl (ParentRecID, DeployIssue) VALUES
    ('ID1', 'Process Issue'),
    ('ID1', 'Timing Issue'),
    ('ID2', 'Process Issue'),
    ('ID3', 'Process Issue'),
    ('ID4', 'Timing Issue'),
    ('ID4', 'Technical Issue');
    -- DDL and sample data population, end
    
    SELECT ParentRecID
    	, DeployIssue = STRING_AGG(DeployIssue, ' | ')
    FROM @tbl
    GROUP BY ParentRecID;
    
    
    
    0 comments No comments