SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Merge column data where an ID is the same
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kieran5405
Yak Posting Veteran

Ireland
94 Posts

Posted - 10/10/2012 :  14:17:37  Show Profile  Reply with Quote
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

Edited by - kieran5405 on 10/10/2012 14:18:06

chadmat
The Chadinator

USA
1974 Posts

Posted - 10/10/2012 :  15:03:33  Show Profile  Visit chadmat's Homepage  Reply with Quote
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
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 10/10/2012 :  15:09:05  Show Profile  Reply with Quote
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
Go to Top of Page

kieran5405
Yak Posting Veteran

Ireland
94 Posts

Posted - 10/10/2012 :  17:27:06  Show Profile  Reply with Quote
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


Edited by - kieran5405 on 10/10/2012 17:28:18
Go to Top of Page

kieran5405
Yak Posting Veteran

Ireland
94 Posts

Posted - 10/11/2012 :  06:33:19  Show Profile  Reply with Quote
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




Edited by - kieran5405 on 10/11/2012 06:36:59
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 10/12/2012 :  14:05:54  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.3 seconds. Powered By: Snitz Forums 2000