Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Software Development Software Development
Search Search
Search for:
Tech Support Guy > > >

Help with SQL!


(!)

sqln00b's Avatar
sqln00b sqln00b is offline
Member with 6 posts.
THREAD STARTER
 
Join Date: Jul 2012
Experience: Beginner
11-Jul-2012, 10:39 AM #1
Help with SQL!
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!

Last edited by sqln00b; 11-Jul-2012 at 10:46 AM..
ckphilli's Avatar
ckphilli   (Chris) ckphilli is offline
Member with 4,290 posts.
 
Join Date: Apr 2006
Location: Down South
Experience: Intermediate
11-Jul-2012, 04:50 PM #2
Quote:
Originally Posted by sqln00b View Post
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!
So your goal is to see:

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

?
ckphilli's Avatar
ckphilli   (Chris) ckphilli is offline
Member with 4,290 posts.
 
Join Date: Apr 2006
Location: Down South
Experience: Intermediate
11-Jul-2012, 05:05 PM #3
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.
sqln00b's Avatar
sqln00b sqln00b is offline
Member with 6 posts.
THREAD STARTER
 
Join Date: Jul 2012
Experience: Beginner
12-Jul-2012, 09:55 AM #4
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!
ckphilli's Avatar
ckphilli   (Chris) ckphilli is offline
Member with 4,290 posts.
 
Join Date: Apr 2006
Location: Down South
Experience: Intermediate
12-Jul-2012, 10:09 AM #5
Quote:
Originally Posted by sqln00b View Post
Sorry, now my goal is to get the data to look like this. AUTO_ID: 1 etc.
"etc" doesn't help. You need to provide a full goal.

Quote:
Originally Posted by sqln00b View Post
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.
Do you mean row as opposed to column? And the copy/pasting part has nothing to do with SQL.
Quote:
Originally Posted by sqln00b View Post
Thank you for all of your help thus far!
No problem.
sqln00b's Avatar
sqln00b sqln00b is offline
Member with 6 posts.
THREAD STARTER
 
Join Date: Jul 2012
Experience: Beginner
12-Jul-2012, 10:21 AM #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?
ckphilli's Avatar
ckphilli   (Chris) ckphilli is offline
Member with 4,290 posts.
 
Join Date: Apr 2006
Location: Down South
Experience: Intermediate
12-Jul-2012, 10:25 AM #7
Much more clear, give me a few...
sqln00b's Avatar
sqln00b sqln00b is offline
Member with 6 posts.
THREAD STARTER
 
Join Date: Jul 2012
Experience: Beginner
12-Jul-2012, 10:27 AM #8
Okay! Thank you!
ckphilli's Avatar
ckphilli   (Chris) ckphilli is offline
Member with 4,290 posts.
 
Join Date: Apr 2006
Location: Down South
Experience: Intermediate
12-Jul-2012, 10:56 AM #9
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.
sqln00b's Avatar
sqln00b sqln00b is offline
Member with 6 posts.
THREAD STARTER
 
Join Date: Jul 2012
Experience: Beginner
12-Jul-2012, 11:10 AM #10
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!
ckphilli's Avatar
ckphilli   (Chris) ckphilli is offline
Member with 4,290 posts.
 
Join Date: Apr 2006
Location: Down South
Experience: Intermediate
12-Jul-2012, 11:13 AM #11
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
sqln00b's Avatar
sqln00b sqln00b is offline
Member with 6 posts.
THREAD STARTER
 
Join Date: Jul 2012
Experience: Beginner
12-Jul-2012, 11:23 AM #12
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.
ckphilli's Avatar
ckphilli   (Chris) ckphilli is offline
Member with 4,290 posts.
 
Join Date: Apr 2006
Location: Down South
Experience: Intermediate
12-Jul-2012, 11:31 AM #13
This link should help: http://msdn.microsoft.com/en-us/library/ms187928.aspx

But in easily understandable, brief terms...you're giving the int space to be converted to a string(chars).
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑