SQL Server : name concatenation - multiple users

Question

I am trying to concatenate multiple users into a singular string. Here is an example of my database:

dbo.Users

| UserId | AccountId | Title | FirstName | LastName |
| 1234   | 1001      | Mr    | John      | Banks    |   
| 1235   | 1001      | Mrs   | Georgia   | Banks    |
| 1236   | 1002      | Mr    | Chris     | Aims     |
| 1237   | 1002      | Mrs   | Caroline  | Hole     |

dbo.Account

| AccountId | SignUpDate | LastLoginDate |
| 1001      | 20/08/2017 | 13/06/2018    |
| 1002      | 20/08/2017 | 13/06/2018    |

I want to be able to get these users in a string like this:

Account 1001: 
Mr J & Mrs G Banks

Account 1002:
Mr C Aims & Mrs C Hole

Can anyone make any suggestions?

EDIT:

The accounts may only have a maximum of 2 users
The accounts may have a singular user
The accounts may have any of the following titles: Miss, Mr, Mrs, Ms, Dr, Prof, Major, Bishop, Rev, Sir, Dame, Lord, Capt, Lady
The accounts should show Title Initial & Title Initial for matching last names
The accounts should show Title Initial LastName & Title Initial LastName for non-matching last names

What version of `SQL Server` and can there be more than 2 users per account? — SQLChao, Jun 13 '18 at 13:51
However, you can have Male Male, Female Female, Male & Female. — Harambe, Jun 13 '18 at 13:55
Voted to re-open: The linked duplicate is not a duplicate... the tricky part here is the surpressed LastName in cases of couples. — Shnugo, Jun 14 '18 at 08:46
Hi Dan, is this question solved? I've reopened it, because it was not a duplicate in my eyes. Well, in a way it is, but there is some additional "salt" included... Anyway: if one of the answers helped you out, it would be kind to accept this answer in order to close this question. Additionally you are asked to vote on any contribution (helpful or not), that's up to you. Happy coding! — Shnugo, Jun 15 '18 at 10:55
I have selected your answer as the accepted answer as this has provided a working and accurate solution to my problem. Thanks. — Harambe, Jun 18 '18 at 11:01

paparazzo · Answer 1 · 2018-06-13T14:12:16.203

0

I will leave the concatenation to you.

This counts on Mr and Mrs records

select * 
from Users mr 
join Users mrs 
  on mr.AccountId = mrs.AccountId 
 and mr.Title  = 'mr' 
 and mrs.title = 'mrs'

edited Jun 13 '18 at 14:12

answered Jun 13 '18 at 14:01

paparazzo

44,497
23
105
176

Working off this answer, here is an existing SO question about concatenation from multiple rows in SQL: https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv – Tingo Jun 13 '18 at 14:17
@Tingo This does not have multiple rows. – paparazzo Jun 13 '18 at 14:55

score -1 · Answer 2 · answered Jun 13 '18 at 14:23

Your expected answer might go like this:

;WITH CTE AS
(
    SELECT  1234  AS UserId , 1001  AS AccountId   , 'Mr' AS Title ,'John' AS FirstName      ,'Banks' AS     LastName   
UNION ALL
    SELECT  1235   ,1001      , 'Mrs'   , 'Georgia'   , 'Banks'    
UNION ALL           
    SELECT  1236   , 1002      , 'Mr'    , 'Chris'     , 'Aims'
UNION ALL 
    SELECT  1237   , 1002      , 'Mrs'   , 'Caroline'  , 'Hole'
)
,CTE1 AS
(
    SELECT UserId,AccountId,Title,FirstName,LastName, Title+SPACE(2)+LEFT(FirstName,1)+SPACE(2)+LastName AS FullName
    FROM CTE
)
,CTE2 AS
(   
    SELECT DISTINCT AccountId,(SELECT ','+FullName FROM CTE1 CC WHERE C.AccountId=CC.AccountId FOR XML PATH('')) AS CombName
    FROM CTE1 C
)
    SELECT 'Account '+ CAST(AccountId AS VARCHAR(10))+' : ' + CHAR(13)+REPLACE(SUBSTRING(CombName,2,LEN(CombName)),',',' & ') AS TotalColumn
    FROM CTE2

Shnugo · Accepted Answer · 2018-06-13T14:46:34.340

Try it like this

DECLARE  @mockup TABLE(UserId INT,AccountId INT,Title VARCHAR(10),FirstName VARCHAR(100),LastName VARCHAR(100));
INSERT INTO @mockup VALUES
 (1234,1001,'Mr','John','Banks')
,(1235,1001,'Mrs','Georgia','Banks')
,(1236,1002,'Mr','Chris','Aims')
,(1237,1002,'Mrs','Caroline','Hole');

SELECT t1.AccountId
      ,STUFF((
        SELECT ' & ' 
               + CONCAT(t2.Title + ' '
                       ,LEFT(t2.FirstName,1) + ' '
                       ,CASE WHEN LEAD(t2.LastName) OVER(ORDER BY t2.UserId)=t2.LastName THEN NULL ELSE t2.LastName END)
        FROM @mockup t2
        WHERE t2.AccountId=t1.AccountId
        FOR XML PATH(''),TYPE
       ).value('.','nvarchar(max)'),1,3,'')
FROM @mockup t1
GROUP BY t1.AccountId;

You wrote in a comment about SQL-Server 2008. This does not allow for LEAD(). You can replace the line with LEAD with this

,CASE WHEN (SELECT TOP 1 LastName FROM @mockup t3 WHERE t3.AccountId =t1.AccountId AND t3.UserId>t2.UserId)=t2.LastName THEN NULL ELSE t2.LastName END)

Hi downvoter, mind to explain? This is a fully working MCVE, with alternative approaches for different versions. Downvotes without a comment are - uhm - cowardish... — Shnugo, Jun 14 '18 at 08:47

SQL Server : name concatenation - multiple users

3 Answers3