Merge column data where an ID is the same

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Merge column data where an ID is the same

Author

Topic

kieran5405
Yak Posting Veteran

96 Posts

Posted - 2012-10-10 : 14:17:37

Hi,

Im going to ask this in the tsql beginners forum as i think this is pretty basic but i cant get it going.

I have a sql server 2008 table called temp1 which has 2 columns - one called 'id' and the other 'name'. The 'name' column will hold all different names...but there will be some repeated IDs. I want to merge the names in the name column for each row where the ID is the same...as i tried below.

I also tried using a group by but its not working either. Any help appreciated.

select p.name + ' & ' + k.name
from temp1 p
inner join temp1 k
on p.id = k.id

chadmat
The Chadinator

1974 Posts

Posted - 2012-10-10 : 15:03:33

Try this (This will not update anything, but it should select what you are looking for):

select p.name + ' & ' + k.name
from temp1 p
inner join temp1 k
on p.id = k.id and p.name != k.name

-Chad

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-10-10 : 15:09:05

Here's one possible way:


select a.id
       ,substring(max(b.names), 4, 1000) as names
from   temp1 a
cross apply (
       select ' - ' + name
       from   temp1
       where id = a.id
       for xml path('')
       ) b (names)
group by a.id

Be One with the Optimizer
TG

kieran5405
Yak Posting Veteran

96 Posts

Posted - 2012-10-10 : 17:27:06

Thanks Chadmat...u got it closer than me but its showing 2 rows instead of the 1!!

But TGs solution works great!!

Thanks guys for your direction!!

quote:

Originally posted by TG

Here's one possible way:


select a.id
       ,substring(max(b.names), 4, 1000) as names
from   temp1 a
cross apply (
       select ' - ' + name
       from   temp1
       where id = a.id
       for xml path('')
       ) b (names)
group by a.id

Be One with the Optimizer
TG

kieran5405
Yak Posting Veteran

96 Posts

Posted - 2012-10-11 : 06:33:19

I have now come across an issue with my below tsql. i have added a 3rd column (address1) to the below...however now i have duplicate rows with the same id and same name...if the column address1 values are not exactly the same. I was trying to do a distinct on the row based on the ID.

I tried to do this using the example code from www but its not working -
SELECT CUST_NUM, ORDER_NUM, ITEM_NUM, TIME
FROM Transactions A
RIGHT JOIN (SELECT DISTINCT CUST_NUM FROM Transactions) AS TR
ON TR.CUST_NUM = A.CUST_NUM

Basically I want to pull back only distinct column ID and ignore all duplicate IDs.

Any advice?

quote:
Originally posted by kieran5405

Thanks Chadmat...u got it closer than me but its showing 2 rows instead of the 1!!

But TGs solution works great!!

Thanks guys for your direction!!

quote:
Originally posted by TG

Here's one possible way:

select a.id
,substring(max(b.names), 4, 1000) as names
from temp1 a
cross apply (
select ' - ' + name
from temp1
where id = a.id
for xml path('')
) b (names)
group by a.id

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-10-12 : 14:05:54

If this is a new topic (unrelated to "Merge column data where an ID is the same") then post a new topic please.
Be sure to include the basic things anyone would need to give you a quick answer: DDL and DML. DDL is a runnable script to create your table(s) including constraints, DML will be insert statements to add some sample data to your table(s). And desired results based on your sample data.

It sounds like you are missing any unique constraints which will keep logical duplicates out of your data.

And this statement doesn't really make sense:

quote:

I tried to do this using the example code from www but its not working -
SELECT CUST_NUM, ORDER_NUM, ITEM_NUM, TIME
FROM Transactions A
RIGHT JOIN (SELECT DISTINCT CUST_NUM FROM Transactions) AS TR
ON TR.CUST_NUM = A.CUST_NUM

Doesn't it have the same result as:


SELECT CUST_NUM, ORDER_NUM, ITEM_NUM, TIME
FROM Transactions A

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources