Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2010

    Question Unanswered: Sybase how to transpose rows to columns

    I have a scenario to transpose rows into columns and insert/update some table.
    Table A:
    id name company address
    1 abc google
    2 xyz yahoo newyork

    The above info is actually stored in below format.
    id field_name value
    1 name mny
    1 address denver
    2 company microsoft

    I get the results in the above format ( id, field_name, value ) and need to insert/update table A. This is required for more than 10000 rows so what would be a best solution in order to implement this?
    Let me know if you have faced such thing and have any ideas.

    Thanks in advace.


  2. #2
    Join Date
    Jun 2010
    1. Main table is t_ca (companyaddress)
    2. Raw data you are getting from table t_raw.

    Do the steps below:
    1. Create index on both tables on id column. In table t_raw, you have more than one occurrences of id so I suggest you use "with allow_dup_row" in create index statement.
    2. Move data from t_raw to a temp table with identity column and loop through identity col in that table. Use while loop. Don't use cursor
    select id, field_name, value, auto_id = identity(5)
    into #temp from t_raw
    3. In the while loop, check for following scenarios:
    1. If id from t_raw exists in t_ca and
      • a. if current field changed then update
      • b. if current field does not exists in t_ca then insert
      • c. if current field does not exists in t_raw then delete (??)
    2. If id from t_raw does not exists in t_ca then delete from t_ca (??)

    It's really a good technique if you are worried about performance.

    Implementing the logic for point 3 is going to be tricky and will require a lot of testing form your side.

    Let us know if you have any question.
    Please always reply to the post if it was helpful. Others may find it helpful.

  3. #3
    Join Date
    May 2005
    South Africa
    Provided Answers: 1
    Use case
    select id
    ,name=min(case when field_name='name' then value end)
    ,company=min(case when field_name='company' then value end)
    ,address=min(case when field_name='address' then value end)
    from sourceA
    group by id
    Last edited by pdreyer; 10-01-10 at 08:48.

Tags for this Thread

Posting Permissions

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