Inserting multiple names in same cell with separated commas

Question

Previously, I was trying to keep ALL previous last names of an employee in a table cell with commas (see below) but I didn’t know how. Someone then suggested using normalization which I’m not sure if it’ll be easier in this situation. My boss wants to display all previous last names of an employee on a web page each time she edits her account info. Simply put, when Judy changes her last name again – her last name Kingsley should be inserted behind Smith. So, my question is back to whether or not it is possible to add multiple last names in the same cell, separated with commas as I thought it’ll be simpler when I use a variable on the web page to display all the Alias at once? Yet, I’ve no clue the complexity to write the codes for this. Any help is truly appreciated.

Current SQL table

+---------------+-----------------+----------------+--------------------+
     People          FirstName         LastName            Alias
+---------------+-----------------+----------------+--------------------+
     002112            Judy             Smith              Hall

Preferred

+---------------+-----------------+----------------+--------------------+
     People          FirstName         LastName            Alias
+---------------+-----------------+----------------+--------------------+
     002112            Judy            Kingsley         Hall, Smith

Is this just for display or does you boss want to enter the change of name in that cell? — Tony Hopkinson, Aug 21 '13 at 15:30
@TonyHopkinson: It's mainly for display, but she thought it'll be easier if I keep all the old last names in the same cell. — Esther, Aug 21 '13 at 16:13
Tell her not to give up her day job then. @TI answer is the one you want. — Tony Hopkinson, Aug 21 '13 at 16:21
She's lucky she's employing you and not me. Otherwise she's have had to deal with me saying that to her face, though possibly with a little more tact. — Tony Hopkinson, Aug 21 '13 at 16:29

score 1 · Answer 1 · edited May 23 '17 at 12:28

Keep the database normalized.

People:

(Id, Firstname, Lastname)

LastnameHistory:

(PeopleId, OldLastname, NewLastname, DateChanged)

You can the create a view which would be a "GROUP_CONCAT" type of query to transform the data as required.

An example:

DECLARE @people TABLE ( id INT IDENTITY(1,1), fname VARCHAR(50), lname VARCHAR(50))
DECLARE @lnameHistory TABLE ( id INT IDENTITY(1,1), people_id INT, lname VARCHAR(50), date_changed DATETIME)

INSERT INTO @people (fname, lname)
VALUES ('john', 'smith'), ('jane', 'doe')

INSERT INTO @lnameHistory (people_id, lname, date_changed)
VALUES (2, 'shakespeare', '2012-01-01'), (2, 'einstein', '2013-12-12')

;WITH group_concat AS
(
    SELECT people_id, LEFT(lnames , LEN(lnames )-1) AS lnames
    FROM @lnameHistory AS o
    CROSS APPLY
    (
        SELECT lname + ', '
        FROM @lnameHistory AS i
        WHERE o.people_id = i.people_id
        ORDER BY date_changed ASC
        FOR XML PATH('')
    ) pre_trimmed (lnames)
    GROUP BY people_id, lnames
)

SELECT p.*, gc.lnames FROM @people p
JOIN group_concat gc ON gc.people_id = p.id

Some reference for syntax:

I read the web page you provided and it has a lot of good information but I'm unfamiliar with syntax in GROUP_CONCAT. — Esther, Aug 21 '13 at 16:22
Maybe this is too much to ask. Can you kindly demonstrate the creation of a View using GROUP_CONCAT? — Esther, Aug 21 '13 at 16:29

Richard Raby · Answer 2 · 2013-08-21T15:49:43.963

0

Assuming your update statement is a stored procedure taking in parameters of @personId and @newLastName:

EDIT

Sorry, wrong version of SQL. Have to do it the old school way!

UPDATE PeopleTable
SET Alias = Alias + ', ' + LastName,
    LastName = @newLastName
WHERE
    People = @personId

edited Aug 21 '13 at 15:49

answered Aug 21 '13 at 15:23

Richard Raby

106
6

I used your codes in my stored procedure but it didn't work. Not sure if I did it correctly. – Esther Aug 21 '13 at 15:34
Did you try `SET Alias = Alias + ', ' + LastName,`? I've just tried it and seems to work fine. – Richard Raby Aug 21 '13 at 15:50
Yes, I did. After I executed it and went back to test my web page, it didn't insert the previous last name in the Alias column. Thanks. – Esther Aug 21 '13 at 15:58

score -1 · Answer 3 · answered Aug 21 '13 at 15:22

-1

When you update the table for a new LastName, use something like this:

UPDATE <table> SET Alias = Alias + ', ' + LastName, LastName = <newLastName>

answered Aug 21 '13 at 15:22

BWS

3,786
18
25

3

And when her boss asks for the find me the employee who used to have this surname? Also what about uniqueness Alias = "Mine,His,Mine" ? and partial reapeats "Hopkinson,Hopkins,Hopkin" – Tony Hopkinson Aug 21 '13 at 15:35
@BWS: This is how my stored procedure looks like:`UPDATE People SET Alias = Alias + ', ' + LastName, LastName = @LastName WHERE CAST(PeopleID AS Varchar(25)) = @PeopleID` – Esther Aug 21 '13 at 15:41
@TonyHopkinson: You're right. There's no way to enforce uniqueness but that's what my boss wants. :( – Esther Aug 21 '13 at 15:45

Inserting multiple names in same cell with separated commas

3 Answers3