Data Manipulation

  • Hello All,

    Can you please help me on this query? I will be very appreciated that since it caused me lot of headeache.

    I had a query returned data like this:

    F1F2T1T2

    836.0 29881 1059 1078

    836.0,836.1 29881 4343

    836.1 29881 561568

    836.1,836.0 29881 3232

    I wanted to sum up the data for T1 and T2, and group by F1 and F2. Data was returned for (836.0,836.1), (836.1,836.0) as 2 seperated rows because data was different, but they were actually not.

    My goal is to create a fake column and turn data for (836.0,836.1), (836.1,836.0) both become (836.0,836.1) so that they will become 1 row in the sum.

    How can I do that? I don't have a clue why they did that, but my developer came to me to ask for help.

    All I want is my data will be returned like this:

    F1F2T1T2

    836.0 29881 1059 1078

    836.1 29881 561568

    836.0,836.1 29881 7575

    Please rescue me!

    Minh

  • Please refer to the link in my signature on how to provide sample data and table structure for this request. You aren't giving us any sample data to work with. You have manipulated data out of one query and expected results. While the expected results are good, not seeing what the data looks like to begin with makes it pretty hard to write a query to manipulate it to the end goal.

    Emphasis on the first line... *read the link in my signature* and provide sample data as illustrated there.

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • Will F1 ever have more than two numbers in it?

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • F1 may have more than 2 values, but they will be treat as seperately.

    Minh

  • I mean, will F1 ever have '123.1, 126.5, 135.4' and you want it to be "the same" as '135.4, 126.5, 123.1' for grouping purposes.

    I don't have time today to write the code for you, but here is how I would approach this. The trick is to build a standard string from F1 and then group/order on the standard string, rather than the actual value of F1. The easiest way to standardize is to always put the lowest value(s) first. You should probably write a user-defined function to accept the value from F1 and do the following.

    1. Parse the elements which are separated by a comma.

    2. Sort the the elements from lowest to highest.

    3. Build a return string from the sorted elements.

    In your query, reference this function and use it for grouping, instead of F1.

    There may be a better way, but that's all I can think of at the moment.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Yes, that is exactly that I like to do. Is there possible.

    Thanks,

    Minh

  • To be clear, if data is '123.1, 135.4, 126.5', I will want it '123.1, 126.5, 135.4' (sorting data in rows) so that I will always have the data the same in order to group them.

    Minh

  • I edited my previous post. My approach to the solution is detailed there. You can search this site using the keywords "parse" and "concatenate" for some code samples on taking apart your string, putting it into a table variable, and reassembling it from the sorted elements.

    One other thought... talk to the authors of the calling application and ask them if they sort those elements before they build those strings. Sometimes someone else says "Sure" and takes you off the hook.

    Sorry but this will be my last post for some time. I may not be able to check back until next week.

    Good luck.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

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

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