SQL Gurus (maybe even excel gurus)

Discussion in 'OT Technology' started by driftwell, Aug 18, 2005.

  1. driftwell

    driftwell New Member

    Joined:
    Oct 7, 2003
    Messages:
    220
    Likes Received:
    0
    I've got data that i'm pulling from 2 tables:

    This is what hte query looks like:

    note_id----customer_id----topic----note----area

    ---1-----------10---------cash-----blah---- print
    ---1-----------10 ------- cash-----blah---- keep
    ---1-----------10------- cash----- blah ----order
    ---2-----------11------- check----wrote---- keep
    ---2-----------11------- check---- wrote ----print
    ---3 ----------11------- other---- other----print

    This is what I WANT the query to look like:

    note_id----customer_id----topic----note----area

    ---1-----------10---------cash-----blah---- print
    -------------------------------------------- keep
    ---------------------------------------------order
    ---2-----------11------- check----wrote---- keep
    ----------------------------------------------print
    ---3 ----------11------- other---- other----print

    "area" is sorta like the options associated with a particular note_id and customer_id

    This is my query string:

    SELECT customer_notepad.note_id,
    customer_notepad.customer_id,
    customer_notepad.topic,
    customer_notepad.note,
    note_area.area
    FROM customer_notepad, note_area
    WHERE customer_notepad.note_id = note_area.note_id
    AND customer_notepad.delete_flag = 'N'
    ORDER BY customer_id


    I tried using the DISTINCT command but SQL says that the data type cannot be selected as distinct.

    This query is also opened in excel... so if there's any way in excel that'd be great.
     
    Last edited: Aug 18, 2005
  2. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    so you want NULLS in the place of the duplicated data? Can i ask why? That doesn't make any sense to me
     
  3. Keyzs

    Keyzs OT Supporter

    Joined:
    Nov 3, 2003
    Messages:
    814
    Likes Received:
    0
    Location:
    Charlotte, MI
    The data is setup incorrectly to do what you want...

    If its scripting or programing you could loop through the data the first time to get distinct note_id, customer_id, topic, note. Then loop back through for the areas.

    Select disctinct note_id, customer_id, topic, note from Customer_Notepad

    For each line
    select area from note_Id where customer_id = previous.customer_id, topic = previous.topic, note = previous.note
    next
     
  4. driftwell

    driftwell New Member

    Joined:
    Oct 7, 2003
    Messages:
    220
    Likes Received:
    0
    the reason why is because my picky superiors say its too hard to "read" on paper... the first 4 fields can be all the same, and thus only the first line (or instance) of it is needed... and then the all the results in the 5'th field.

    Keyzs... you're right... after a bit of reading i figured that the data just won't work the way i want it to.

    So i took it into excel and wrote a VB script to loop through each cell and delete dups... and just ran it through each field except the last. Very similar to your suggestion.

    Thanks ALL!
     
  5. saddened

    saddened New Member

    Joined:
    Aug 23, 2005
    Messages:
    3
    Likes Received:
    0
    You could do something similar to what your looking for by using a GROUP_CONCAT (with mysql anyhow). It would result in the last column being a comma delimited list of values that had all of the other columns in common.

    1 --- 10 --- cash --- blah --- print,keep,order
     
  6. Keyzs

    Keyzs OT Supporter

    Joined:
    Nov 3, 2003
    Messages:
    814
    Likes Received:
    0
    Location:
    Charlotte, MI
    There isn't such a equivalent in Excel (or any other MS based SQL)...
     

Share This Page