merging 2 rows into 1 row

Hi ,
i have 2 tables
table a
m_id name
1 abcd
2 bcde

table b
m_id id org_name ind_name
1 1192 gold coast
1 1193 fred
2 1196 greg
2 1198 TechTronics

i am now generating the report through a sql query whose output shud be
id Id Org_name Ind_name
1 1192 or 1193 gold_coast fred
2 1196 or 1198 TechTronics greg

How to achieve this?
i tried self join but that is giving me the message no more spool space(the original table is bigger about 2 million records)?

Hi,

You can try the below query to achieve for merging 2 rows in to 1 row value.

Create Table Test (m_id int, org_name varchar(50))

Insert into Test values (1, 'gold coast')
Insert into Test values (1, 'fred')
Insert into Test values (2, 'gred')
Insert into Test values (2, 'TechTronics')

select * from test

select a.m_id, a.org_name + ' ' + b.org_name
from
(select m_id,t1.id, t1.org_name from (
select m_id, row_number() over (partition by m_id order by m_id) id, org_name from test) as t1
where t1.id%2 = 0) as a
Inner Join
(select m_id,t1.id, t1.org_name from (
select m_id, row_number() over (partition by m_id order by m_id) id, org_name from test) as t1
where t1.id%2 <> 0 ) as b
on a.M_id = b.m_id

Please get back to me once you tried.

Venkataraman M
MCP SQL BI 2005
9940198217
ramanmahalingam@hotmail.com