Concatenate values with LEFT join

Question

While searching, I came across a very similar post here but I have an additional question to what has been posted there already.

id|person_name|department_name|phone_number
--+-----------+---------------+------------
1 |"John"     |"Finance"      |"023451"
1 |"John"     |"Finance"      |"99478"
1 |"John"     |"Finance"      |"67890"
1 |"John"     |"Marketing"    |"023451"
1 |"John"     |"Marketing"    |"99478"
1 |"John"     |"Marketing"    |"67890"
2 |"Barbara"  |"Finance"      |""
3 |"Michelle" |""             |"005634"

Lets say I want the final result as:

id|person_name|department_name|phone_number
--+-----------+---------------+------------
1 |"John"     |"Finance"      |"023451", "99478", "67890"
1 |"John"     |"Marketing"    |"023451", "99478", "67890"
2 |"Barbara"  |"Finance"      |""
3 |"Michelle" |""             |"005634"

basically similar results from phone_number concatenated; then can you advise what should I be doing ? I tried GROUP_CONCAT with DISTINCT but it didn't help.

Why did you tag every RDBMS? Regardless, there are so many answers for this specific question on SO for each that you should not have any problem finding the answer. — dfundako, Jul 19 '18 at 19:27
Lots of Db servers tagged, which do you want a solution for? — P.Salmon, Jul 19 '18 at 19:27

score 0 · Answer 1 · answered Jul 19 '18 at 19:35

So this will pivot and comma delimit your Numbers which I believe is the desired effect? This is a SQL Server solution

declare @t table (OrderedID int, EmpName varchar(50), EmpDep varchar(50), Num varchar(50))

insert into @t
values
(1,'John','Dep1','123')
,(1,'John','Dep1','456')
,(1,'John','Dep2','789')
,(2,'Doug','Dep1','987')
,(2,'Doug','Dep1','654')
,(2,'Steve','Dep2','321')


Select
*
From @t

SELECT distinct e.EmpName,
  e.EmpDep,
  LEFT(r.Num , LEN(r.Num)-1) num
FROM @t e
CROSS APPLY
(
    SELECT r.Num + ', '
    FROM @t r
    where e.EmpName = r.EmpName
      and e.EmpDep = r.EmpDep
    FOR XML PATH('')
) r (Num)

Ouput:

EmpName EmpDep  num
Doug    Dep1    987, 654
John    Dep1    123, 456
John    Dep2    789
Steve   Dep2    321

Concatenate values with LEFT join

1 Answers1