Concatenate rows together through SQL query

[xoxoxoxoxoxox]

Hi, How can i concatenate two rows together? I have a table with id and three columns. I want to display all the colunms with same id in a row. Original Table: id column1 column2 column3 1 a b c 1 d e f 2 g h i 2 k l m Desired Result: id column1 column2 column3 column4 column5 column6 1 a b c d e f 2 g h i k l m So rows are shifted up where ID is same. Is it possible to get this result through query? I'll very much appreciate any help. Thanks

[xoxoxoxoxoxox]

nobody is caring about this poor guys.:sigh:

[Eric Dahlvang]

Maybe someone else has a better idea, but I can't think of a pretty way to do this. If the data is ALWAYS in two records, you could try something like:

select ids.id,
(select top 1 column1 from mytesttable t2 where t2.id = IDs.id order by column1) as column1,
(select top 1 column2 from mytesttable t2 where t2.id = IDs.id order by column1) as column2,
(select top 1 column3 from mytesttable t2 where t2.id = IDs.id order by column1) as column3,
(select top 1 column1 from mytesttable t2 where t2.id = IDs.id order by column1 desc) as column4,
(select top 1 column2 from mytesttable t2 where t2.id = IDs.id order by column1 desc) as column5,
(select top 1 column3 from mytesttable t2 where t2.id = IDs.id order by column1 desc) as column6
from (select distinct id from mytesttable) as IDs

Any other solution would probably involve a cursor and/or temporary table.

--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters

[xoxoxoxoxoxox]

Thank you for your solution. I have over 100 colunms in some of my tables so i guess using cursor and temp table would be the choice. Anyway, thanks for the idea.