December 7, 2011 at 7:51 am
Hi,
I have a table that contains an identifier and some name/address details some of the records have the same ID number but different details E.G.
ID Name Address
1 Billy 10 Oak terrace
1 Johnny 12 Oak terrace
How can I copy this data to another table so it looks like this. It becomes a single, rather than double line entry.
ID Name1 Name2 Address1 Address2
1 Billy Johnny 10 Oak terrace 12 Oak terrace
Many Thanks
December 7, 2011 at 8:10 am
Why would you want to do that to your data? How you are going to handle this when there are 3 with the same ID? What about when there are 27? This has the look of a report or something along those lines. That should be handled at the time you want to display the data and not the way you store it.
_______________________________________________________________
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/
December 7, 2011 at 8:12 am
There will only ever be two of the same ID number due to the nature of the data.
December 7, 2011 at 9:08 am
Well I think you find that you should keep this data separate but of course I don't know all the details of your project. There is probably a more elegant way to do this but something like this should work.
;with SampleData(ID, Name, Addr)
as
(
select 1, 'Billy', '10 Oak terrace' union all
select 1, 'Johnny', '12 Oak terrace' union all
select 2, 'Somebody', '123 any street' union all
select 2, 'Tom', '321 your street' union all
select 3, 'Harry', '3203 Some other street'
)
select sd1.ID, sd1.Name, sd1.Addr, sd2.Name, sd2.Addr from
(
select ID, Name, Addr from
(
select sd.ID, sd.Name, sd.Addr, ROW_NUMBER() over (partition by sd.ID order by sd.name) as RowNum
from SampleData sd
) x
where RowNum = 1
) sd1
left join
(
select ID, Name, Addr from
(
select sd.ID, sd.Name, sd.Addr, ROW_NUMBER() over (partition by sd.ID order by sd.name) as RowNum
from SampleData sd
) x
where RowNum = 2
) sd2 on sd2.ID = sd1.ID
_______________________________________________________________
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/
December 7, 2011 at 9:25 am
Hi,
Thank you for the reply. Looks like it'll do the trick. I agree the data isn't in an ideal format. But i'll have to make the best of a bad job.
Thanks
December 7, 2011 at 9:45 am
rpeplow (12/7/2011)
Hi,Thank you for the reply. Looks like it'll do the trick. I agree the data isn't in an ideal format. But i'll have to make the best of a bad job.
Thanks
You're welcome. Sadly that is sometimes the best we can do. 😉
_______________________________________________________________
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/
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply