1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Help with SQL!

Discussion in 'Software Development' started by sqln00b, Jul 11, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. sqln00b

    sqln00b Thread Starter

    Joined:
    Jul 11, 2012
    Messages:
    6
    Hello everyone!

    I am very new to SQL, knowing the basics of writing Queries, and joining tables. I have an assignment with the company that I am working for this summer to work on a lot of SQL data.

    I am running on Windows XP, working with Oracle or SQALL.

    I need to take this data, and format it into a certain format. I will copy and paste the Query that I have so far. I am going to make an example table to help you better understand how I need to format this data.

    select auto_id || id_number
    from table1

    I know that the "||" is a Concatenation Operator and that it joins the two columns of data.

    Here is the sample table

    AUTO_ID ~ ID_NUMBER ~ ID_CARD ~ FIELD_ID
    ---------------------------------------------
    1...............34580935........BOB................7
    2...............45546428........JIM.................8
    3...............98976756.......CAROL..............9
    4...............58908656.......TIMMY............10
    5...............54685754......JESSICA...........11
    6...............35453486.....MICHELLE..........12


    I need to format the data so that it looks like this, I will take the AUTO_ID field as an example/
    The first line of data e.g.
    Auto_ID;1,

    I don't know how to make a symbol go inbetween the Concatenated data, or get the field name in the data. Is it a pipeline function? If so, could you please elaborate on what that is? I've googled a lot about this, and being a SQL beginner, it's getting into subqueries which is a subject I am not familiar with at all.

    Any help is appreciated, thank you!
     
  2. ckphilli

    ckphilli

    Joined:
    Apr 29, 2006
    Messages:
    4,384
    So your goal is to see:

    Auto_ID;1
    Auto_ID;2
    Auto_ID;3
    Auto_ID;4
    Auto_ID;5
    Auto_ID;6

    ?
     
  3. ckphilli

    ckphilli

    Joined:
    Apr 29, 2006
    Messages:
    4,384
    If you are in fact doing something like that, here's what you need (as an example):

    Code:
    select 'Auto_ID;' + CAST(Auto_ID AS char(12))
    from table1
    You have to convert int to char depending on your data.
     
  4. sqln00b

    sqln00b Thread Starter

    Joined:
    Jul 11, 2012
    Messages:
    6
    Sorry, now my goal is to get the data to look like this. AUTO_ID: 1 etc.

    But I must combine it with another field of data, making it all into one column, then when it's copy and pasted, it has a line break.

    Thank you for all of your help thus far! :)
     
  5. ckphilli

    ckphilli

    Joined:
    Apr 29, 2006
    Messages:
    4,384
    "etc" doesn't help. You need to provide a full goal.

    Do you mean row as opposed to column? And the copy/pasting part has nothing to do with SQL.
    No problem.:)
     
  6. sqln00b

    sqln00b Thread Starter

    Joined:
    Jul 11, 2012
    Messages:
    6
    My apologies on the etc part. The data needs to look like this

    AUTO_ID: 1 FIELD_ID: 7
    AUTO_ID: 2 FIELD_ID: 8
    AUTO_ID: 3 FIELD_ID: 9
    AUTO_ID: 4 FIELD_ID: 10

    I also need to insert a linefeed into there somewhere, if that makes any sense? So that when the data is exported into Excel, it will appear as this.

    AUTO_ID: 1
    FIELD_ID: 7
    AUTO_ID: 2
    FIELD_ID: 8

    Make any more sense?
     
  7. ckphilli

    ckphilli

    Joined:
    Apr 29, 2006
    Messages:
    4,384
    Much more clear, give me a few...
     
  8. sqln00b

    sqln00b Thread Starter

    Joined:
    Jul 11, 2012
    Messages:
    6
    Okay! :) Thank you!
     
  9. ckphilli

    ckphilli

    Joined:
    Apr 29, 2006
    Messages:
    4,384
    Okie doke...here's the query:
    Code:
    select 'AUTO_ID: ' + CAST(AUTO_ID AS CHAR(12)) + CHAR(13) + CHAR(10) + 'FIELD_ID: ' + CAST(FIELD_ID AS CHAR (12)) AS 'Combined'
    from table1
    
    This is assuming your ID's are int. If they are char, there's no need for the conversion.

    CHAR(13) + CHAR(10) is the line break. You will not see this in the query, only after the export.

    This will hopefully get you going. I'm not saying this is the end all be all, but given your parameters...it's a start.
     
  10. sqln00b

    sqln00b Thread Starter

    Joined:
    Jul 11, 2012
    Messages:
    6
    Thank you so much! I tried entering it into Oracle, and it said there was a missing value on the second CHAR. I will try and figure it out though, you've helped me so much! :)
     
  11. ckphilli

    ckphilli

    Joined:
    Apr 29, 2006
    Messages:
    4,384
    No problem. There are some slight differences I've noticed with Oracle but I'm sure you can toy with it.

    One thing of note...when you do the conversion if you have say CHAR(1) instead of CHAR(12) you'll get an asterisk depending on the length of your data. So play with it. Enjoy:)
     
  12. sqln00b

    sqln00b Thread Starter

    Joined:
    Jul 11, 2012
    Messages:
    6
    Do you mind explaining the CHAR(1) and CHAR(12) items to me? I don't understand how the different numbers corrolate with the CHAR value.
     
  13. ckphilli

    ckphilli

    Joined:
    Apr 29, 2006
    Messages:
    4,384
  14. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1060534