[sqln00b]

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!

[ckphilli]

Originally Posted by sqln00b

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]

If you are in fact doing something like that, here's what you need (as an example):

select 'Auto_ID;' + CAST(Auto_ID AS char(12))
from table1

You have to convert int to char depending on your data.

[sqln00b]

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]

Originally Posted by sqln00b

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.

Originally Posted by sqln00b

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.

Originally Posted by sqln00b

Thank you for all of your help thus far!

No problem.

[sqln00b]

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]

Much more clear, give me a few...

[sqln00b]

Okay! Thank you!

[ckphilli]

Okie doke...here's the query:

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]

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]

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]

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]

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).