| Author |
 Topic  |
|
|
kieran5405
Yak Posting Veteran
Ireland
92 Posts |
 Posted - 10/10/2012 : 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 |
Edited by - kieran5405 on 10/10/2012 14:18:06
|
|
|
chadmat
The Chadinator
USA
1961 Posts |
Posted - 10/10/2012 : 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
Flowing Fount of Yak Knowledge
USA
5469 Posts |
Posted - 10/10/2012 : 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
Ireland
92 Posts |
Posted - 10/10/2012 : 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
|
Edited by - kieran5405 on 10/10/2012 17:28:18 |
|
|
|
kieran5405
Yak Posting Veteran
Ireland
92 Posts |
Posted - 10/11/2012 : 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
|
Edited by - kieran5405 on 10/11/2012 06:36:59 |
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5469 Posts |
Posted - 10/12/2012 : 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 |
|
|
| |
Topic |
|
Merge column data where an ID is the same
