CONCATENATE a filed if the rows have the same Id

Question

CONCATENATE a filed if the rows have the same Id

rs-337036

Ten Centuries

Points: 1105
More actions
July 23, 2015 at 2:17 pm

#306020

I want to CONCATENATE a field if the rows have the same Id. For example the data looks like
ID Name Identifier
1 ken English
1 Jack French
2 Jamie Spanish
3 Shirley German
The data should look like this if the IDs are same. I am concatenating the Identifier column with the name if the ids are same
ID Name Identifier
1 Ken English English
1 Jack French French
2 Jamie Spanish
3 Shirley German

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

rs-337036 (7/23/2015)
I want to CONCATENATE a field if the rows have the same Id. For example the data looks like
ID Name Identifier
1 ken English
1 Jack French
2 Jamie Spanish
3 Shirley German
The data should look like this if the IDs are same. I am concatenating the Identifier column with the name if the ids are same
ID Name Identifier
1 Ken English English
1 Jack French French
2 Jamie Spanish
3 Shirley German

I have to say that your data and your output don't make a lot of sense. So for each row if there is another row with the same ID you want to concatenate Name + Identifier?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

rs-337036 Ten Centuries Points: 1105 More actions · Answer 2

Yes if the row has the same ID i want to Concatenate the Identifier column with the Name column

pietlinden SSC Guru Points: 63500 More actions · Answer 3

start here:

http://www.sqlservercentral.com/articles/comma+separated+list/71700/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

I agree with Sean, this is kind of weird. However, here's a way to accomplish it.

IF OBJECT_ID('SampleData', 'U') IS NOT NULL

DROP TABLE SampleData;

CREATE TABLE SampleData(

ID int,

Name varchar(100),

Identifier varchar(100)

);

INSERT INTO SampleData VALUES

(1, 'ken', 'English'),

(1, 'Jack', 'French'),

(2, 'Jamie', 'Spanish'),

(3, 'Shirley', 'German');

SELECT ID,

CASE WHEN COUNT(*) OVER(PARTITION BY ID) > 1

THEN Name + ' ' + Identifier

ELSE Name END AS Name,

Identifier

FROM SampleData;

GO

DROP TABLE SampleData;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

rs-337036 Ten Centuries Points: 1105 More actions · Answer 5

rs-337036

Ten Centuries

Points: 1105

July 23, 2015 at 3:19 pm

#1814591

Thanks

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 6

SELECT ID,

CASE WHEN COUNT(*) OVER(PARTITION BY ID) > 1

THEN ISNULL(Name + ' ' + Identifier, Name)

ELSE Name END AS Name,

Identifier

FROM SampleData;

Edit...seems you have already changed your mind re NULLS .....??? 🙂

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

rs-337036 Ten Centuries Points: 1105 More actions · Answer 7

rs-337036

Ten Centuries

Points: 1105

July 23, 2015 at 3:40 pm

#1814599

I figured it out that's why.

Thanks

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 8

rs-337036 (7/23/2015)
I figured it out that's why.
Thanks

ok...glad to hear it.

seems I was posting my response whilst you were editing yours. no worries

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day