SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Hybrid View

  1. #1
    SitePoint Zealot fredmintah's Avatar
    Join Date
    Jun 2004
    Location
    Ghana
    Posts
    102
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL Help : Manipulating rows and columns

    Hello guys,
    I am having a problem and it will be a great pleasure for me if this wonderful community is able to help me out.
    I need to extract some information from sql server and manipulate the row and columns that appear.
    What I want to do is to put some rows into a different column; And that will only appear when a row has duplicate value.
    Example . When I excute the sql statement this is the output I get. The informatio is coming from the same table.

    NB: Columns are separate with vertical bars.

    SampleNo | Original Value | Processed Value
    -------------------------------------------
    12345 | 2.34 | 2.30
    12345 | 2.34 | 3.36 *
    00004 | 3.00 | 3.40
    00055 | 8.20 | 8.20
    56789 | 5.21 | 5.00
    56789 | 5.21 | 5.20 *


    I want to execute the sql statement and get output as show below as from the example above. The Duplicate_Value was created in the sql statement.

    SampleNo | Original_Value | Processed_Value | Duplicate_Value
    ------------------------------------------------------------
    12345 | 2.34 | 2.30 | 3.36
    00004 | 3.00 | 3.40 |
    00055 | 8.20 | 8.20 |
    56789 | 5.21 | 5.00 | 5.20

    As you can see, if the SampleNo appear twice but with different Processed_Value value 'rows with asterisks', then a new column has to be generated and the second value put in the generated column. I want to do this in order to take out duplicate values from the output and put the new value matching the duplicate values into the generated column.
    As


    NB: Row with same values should be eliminated.


    I know you guys will bail me out.

    Regards.
    Frederick

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you haven't explained where the asterisk comes from

    in other words, how do you know which row has the processed value and which row has the duplicate value?
    Code:
    select t1.SampleNo
         , t1.Original_Value
         , t1.Processed_Value
         , t2.Processed_Value as Duplicate_Value
      from yourtable as t1
    left outer
      join yourtable as t2
        on t1.SampleNo
         = t2.SampleNo
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot fredmintah's Avatar
    Join Date
    Jun 2004
    Location
    Ghana
    Posts
    102
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    More work on query

    Hi r927,
    I will like to thank you r927 for replying my post and also to thank all those who read my post.
    About y post. The asterisk you see is not part of the result I get from the query but it is just an indication of the rows which is being repeated.
    When I execute your query, it displays 4 rows if it finds a matching SampleNo with only one row 'second row from output' being the output I want and displays 1 row 'as I want' if it does not find a matching SampleNo.
    I do not want a sampleNo to repeated in the output. Can you please have a look at your query again a help me out?

    Thanks

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    my query is fine (i've done this before)

    can you show me your data?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot fredmintah's Avatar
    Join Date
    Jun 2004
    Location
    Ghana
    Posts
    102
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post

    [pre]Hi r927,
    Thanks again for you quick response. I would have wish to send the data structure I'm using but the file is big. I will like you to have the best understanding of what I want you to help me do. My email address is fmintah@gsrgh.com. If you can do me a favour and send me a message with the subject 'acQuire ADM', I will not hesitate to send my database structure to you. I hope I've been forgiven for not attaching the file. But I will include a word document to explain what I will like to achieve
    I will at this moment like to give you details of what exactly I want to do and I'm sorry if you did not get my question right.
    I will like to display some records in the database and the conditions are
    1. SampleNo should not appear more than once in the output.

    2. If SampleNo appears more than once in the database, then put the value in the Processed_Value column of the repeated SampleNo in the database into the generated column 'Duplicate_Value'. As you can see from the first example.

    3. If the SampleNo does not repeat itself, the Duplicate_Value column should be null.

    As you can see in my first question, the SampleNo's which are repeated are those with the asterisk beside it. PLEASE NOTE: The asterisk is not part of the output. It is just an indication to show you which row are being repeated but with different value in the Processed_Value column.
    When you again look at the output I gave in the the example, you can see that the duplicate SampleNos is gone but the new value in the Processed_Value column of the repeated row is now in the new column called Duplicate_Value

    I really appreciate your effort so far and I hope you'll be with me till it is solved.
    I will not hesitate to send the database structure to you.

    Thanks
    Attached Files Attached Files

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by fredmintah
    If SampleNo appears more than once in the database, then put the value in the Processed_Value column of the repeated SampleNo in the database into the generated column 'Duplicate_Value'.
    i'm sorry, but databases don't work that way, there is no way to distinguish between the two rows except by value (i.e. not by position)

    this means you do not know which one is the duplicate -- there is no such thing as "first one" and "duplicate one", they are both equal, except one of the has a value of 3,2 and the other one has a value of 3.3

    and by wanting only one row of output per SampleNo, you must use GROUP BY

    what database are you on? if it's mysql, you could use the GROUP_CONCAT function
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot fredmintah's Avatar
    Join Date
    Jun 2004
    Location
    Ghana
    Posts
    102
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thanks

    I am very grateful for the help given me so for. I should have paid a consultancy fee; don't you think so :-). I am using SQL Server 2000. I have tried using batches and it did not work as well.
    Once I know there is no way to do such a thing, I have to think of another way of doing that.
    I browsed through site and found interesting stuff over there. I guess you're really a Database guru.
    If you have any suggestion on how I'm to do things, I will not hesitate to listen.
    Thanks so much.


Bookmarks

Posting Permissions

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