Post reply

Need to store more then two ids in single column

  • April 9, 2019 at 8:52 am

    #3053908

    Hi Sir,

    I have a requirement to store if one main id have more then two child ids in single column like

    If main id is 1234 and it has two child ids like 2222 and 3333 then output should be in single row instead of two or more rows.

    Current result is,

    Main ID Child_ID

    1234 2222

    1234 3333

    Expected Result should be,

    Main ID Child_ID

    1234 2222,3333

    Table script,


    CREATE TABLE MAIN
    (main_id INT,
    child_id INT)

    INSERT INTO MAIN VALUES (1234,2222);
    INSERT INTO MAIN VALUES (1234,3333);

  • April 9, 2019 at 11:56 am

    #3058852

    No, don't change the storage to have multiple values. That will cause a million and one problems. You're talking about just a display issue in the results. That is best solved programmatically, either on the output application or in the T-SQL. In the T-SQL what you want is a way to concatenate the values. Here is an article laying out all the different methods, their strengths and weaknesses. This will get you what you need. Don't change the way the data is stored. That's a bad plan.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • April 9, 2019 at 1:32 pm

    #3061210

    Agree with Grant.This is a short sighted request  and isn't a requirement. What a requirement would be is to present the data in this way. The way to store this is separate and combining these two into a column will cause performance and data integrity issues later.

     

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply