SQL Server column shown as bounded for duplicate key
Please help me write the SQL query. I have a table Name
with these columns and some sample data:
ID FIRSTNAME SURNAME FULLNAME
---------------------------------------------------
1 JOHN PETER JOHN PETER
2 PETER JACKSON PETER JACKSON
3 JOHN PAUL JOHN PAUL
3 JOHN SECONDNAME JOHN SECONDNAME
Fullname
- this is first name + space + last name, and ID can be repeated.
I want to add one more column OTHERNAMES
: it will be CONCATENATE with all the first and last names of the person (ID) with a separator;
The table should look like this:
ID FIRSTNAME SURNAME FULLNAME OTHERNAMES
------------------------------------------------------------------------
1 JOHN PETER JOHN PETER JOHN PETER
2 PETER JACKSON PETER JACKSON PETER JACKSON
3 JOHN PAUL JOHN PAUL JOHN PAUL;JOHN SECONDNAME
3 JOHN SECONDNAME JOHN SECONDNAME JOHN PAUL;JOHN SECONDNAME
+3
Aiden
source
to share
1 answer
This design is terrible! Do not do this!
Just for completeness:
CREATE TABLE MockUp(ID INT
,FIRSTNAME VARCHAR(100)
,SURNAME VARCHAR(100)
,FullName AS FIRSTNAME + ' ' + SURNAME);
INSERT INTO MockUp VALUES
(1,'JOHN','PETER')
,(2,'PETER','JACKSON')
,(3,'JOHN','PAUL')
,(3,'JOHN','SECONDNAME');
SELECT ID
,FIRSTNAME
,SURNAME
,FullName
,STUFF((SELECT '; ' + x.FullName FROM MockUp AS x WHERE x.ID=m.ID FOR XML PATH(''),TYPE).value('text()[1]','nvarchar(max)'),1,2,'') AS OtherNames
FROM MockUp AS m
GO
DROP TABLE MockUp;
You can see that I have added FullName as a calculated column . Other names are computed on the fly. Don't store calculated data unless you have a good reason!
+3
Shnugo
source
to share