Results 1 to 2 of 2

Thread: merging 2 rows into 1 row

  1. #1
    Join Date
    Dec 2008
    Posts
    1

    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)?

  2. #2
    Join Date
    Mar 2006
    Posts
    5

    solution for merging 2 rows into 1

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •