December 18, 2008 at 2:00 pm
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
December 18, 2008 at 3:53 pm
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.
December 18, 2008 at 4:01 pm
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
December 18, 2008 at 4:22 pm
F1 may have more than 2 values, but they will be treat as seperately.
Minh
December 19, 2008 at 12:55 pm
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
December 19, 2008 at 3:40 pm
Yes, that is exactly that I like to do. Is there possible.
Thanks,
Minh
December 19, 2008 at 3:42 pm
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
December 19, 2008 at 3:45 pm
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