Query data with manipulation

  • Hi,

    I am facing a problem to extract data from a table, the table contains over 41k records, and some of them(>5k) share same column value, now what i am requested is to merge records share same column value.

    Take an example as following

    Name Value Status

    ABC ANYTHING RED

    DEF SOMETHING GREEN

    ABC ANYTHING GREEN

    XYZ NOTHING YELLOW

    I need to run a query that give me output like this, combine 2 records into 1, is this can be done?

    Name Value Status

    ABC ANYTHING RED, GREEN

    DEF ANYTHING GREEN

    XYZ NOTHING YELLOW

    appreicate any help or suggestion.

    Thanks,

    Jack

  • Yes. There are probably more than a couple of ways of doing it, let me share with you the good-n-old pivot table way; not doing your job but here is the pseudo code.

    create temporary table mytable(name, value, extended_status)

    insert into mytable(name,value) select distinct(name,value) from your_base_table

    populate mytable(extended_status) from your_base_table

    select name,value,extended_status from mytable

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • jack_hc (10/30/2008)


    Hi,

    I am facing a problem to extract data from a table, the table contains over 41k records, and some of them(>5k) share same column value, now what i am requested is to merge records share same column value.

    As a simple output or are they suggesting the colossal mistake of storing the data in the database like that?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Does this helps...

    I have written some thing like that earlier... this might not be good per performance wise... but this will give you a start ...

    Click here for link [/url]

    Hope this helps,

    Imran.

  • Um... heh... no, sorry... pointing someone to a Cursor Usage URL is not what I'd call a "good start" even if it has a better answer in the post that followed. 😛

    If you want to make the mistake of creating CSV's in a data base, at least do it with some performance in mind...

    http://www.sqlservercentral.com/articles/Test+Data/61572/

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • PaulB (10/31/2008)


    Yes. There are probably more than a couple of ways of doing it, let me share with you the good-n-old pivot table way; not doing your job but here is the pseudo code.

    create temporary table mytable(name, value, extended_status)

    insert into mytable(name,value) select distinct(name,value) from your_base_table

    populate mytable(extended_status) from your_base_table

    select name,value,extended_status from mytable

    thank you Paul, i'll try that.

  • Jeff Moden (10/31/2008)


    jack_hc (10/30/2008)


    Hi,

    I am facing a problem to extract data from a table, the table contains over 41k records, and some of them(>5k) share same column value, now what i am requested is to merge records share same column value.

    As a simple output or are they suggesting the colossal mistake of storing the data in the database like that?

    just extract data into a excel/csv file would be fine.

  • As always I would say do this in your front end application if you want to show it there

    or in SQL Server 2005, you can also use

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254


    Madhivanan

    Failing to plan is Planning to fail

  • jack_hc (11/3/2008)


    Jeff Moden (10/31/2008)


    jack_hc (10/30/2008)


    Hi,

    I am facing a problem to extract data from a table, the table contains over 41k records, and some of them(>5k) share same column value, now what i am requested is to merge records share same column value.

    As a simple output or are they suggesting the colossal mistake of storing the data in the database like that?

    just extract data into a excel/csv file would be fine.

    Then, the STUFF method that Madhivanan provided the link for above should work a treat for the problem.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 9 posts - 1 through 8 (of 8 total)

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