Breaking Down A Number Column From A Query?

Sep 18, 2007

I’m okay with simple queries but as I’m no expert and have failed to find perhaps the correct wording to describe this method, if at all possible to do, so I have come to ask here.

What I would like to do is take a column from a query and then break down that column into separate results.

So the full query results:
36,18/09/2007 10:00:00,NULL,000102000304,NULL

The column I would like to brake down is (Unique Reference Number):
000102000304

And I would like to break it down to get the last 2 parts (0003 and 04):
0001 | 02 | 0003 | 04

Is this possible to do?
If so where should I be looking or what should I be looking at?

Many Thanks

View 3 Replies


ADVERTISEMENT

Breaking Down A Column

Aug 9, 2000

I currently have a Name column in one of my tables that is in the following format:

Name
Doe, John P

and I need to break this down into three separate fields in a different table:

Last_Name First_Name Middle_Name
Doe John P

Could someone please help me with this issue?

Thanks,

Peter

View 1 Replies View Related

Breaking Apart Column Into Rows

Dec 21, 2005

I have a column that has text delimited by a percent sign that I wishto turn into rows.Example:A column contains ROBERT%CAMARDA, I want to turn that into two rows,one row with ROBERT and antoher row with CAMARDA.I will have source rows that have zero, one, or many percent signdelimiters that will correspond to that many rows (One percent signwill create 2 rows, 2 percent signs will create 3 rows and so forth).Any thoughts?TIARob

View 1 Replies View Related

Breaking Column Data Using File Layouts

Apr 8, 2004

Hi DBA's,

I need to break up a 34 byte column into several columns. Here is my problem.

Table A:

Col1..Col2..Col3..Col4..Col5..Col6..Col7..Col8..Co l9..Col10

Col 9 is char (2) and Col10 is char (34). It is this column that needs to be broken up into several columns depending on the value in Col 9.

Col1 to Col3 is the key to the record.

So say if record 1 has Col 9 value 'AA' then Col10 ( 34 bytes) is to be spilt into 10+10+10+4 (four columns).The value 'AA' can repeat for several records and the value in Col 10 can change for the same value 'AA'.

Now say record 27 has Col 9 value 'BB' then Col 10 is to be split as 5+25+4 (3 columns).

There are 15 such unique values of Col 9. I have the file layouts for Col 10 for each distinct value of Col 9. So using the file layouts and Table A which exists in my database how do I proceed.

Need I make 15 tables ( one each for the 15 unique Col 9 values). These structure
Col1..Col2..Col3...Col9 (the key fields and Col 9) will be common to every table. Plus the file layouts will serve as additional columns specific to each of these tables.

Kindly help.

Vivek

View 14 Replies View Related

Char(10) Breaking Query

Aug 6, 2004

I have been working on a pretty ugly stored procedure recently, while debugging I added a char(10) to the end of each line of the SQL query so I could copy it to query analyzer(QA) and debug the SQL syntax output from of the stored procedure.

It had no effect on the stored procedure working, but when I copied the query to QA it got the error below, so I removed them all and added them in one line at a time to find the problem.

--Server: Msg 170, Level 15, State 1, Line 3
--Line 3: Incorrect syntax near ','.

Below are the 2 querys, the only difference is the Char(10) between Amt6 and Amt7!

http://www.rakbiz.com/download/broken.txt
http://www.rakbiz.com/download/working.txt


I also tried char(13) with same results.

any ideas why this is happening or how to add line breaks for readability without this problem?

Thanks for your help!

View 3 Replies View Related

Breaking Query On Basis Of Conditions

Mar 20, 2008

 On a webpage, there are filters to choose from. Like date, amount, SSN (multiple filters can be choosen).I have a single query so far.   SqlCommand cmd = new SqlCommand("SELECT [column1], [column2], [column3],  [column4], [column5] FROM [table] WHERE [column4] = 'condition4'  AND [column5] = @total_bill AND [last_change] >= @txtStartDate AND [last_change] <= @txtEndDate  ", Conn) ;          cmd.Parameters.Add(new SqlParameter("@total_bill", total_bill1.Text));         cmd.Parameters.Add(new SqlParameter("@txtStartDate", txtStartDate.Text));           cmd.Parameters.Add(new SqlParameter("@txtEndDate", txtEndDate.Text));  I want to break the query so that it executes on the basis of different sets of conditions (filters). If I dont select date filter, then the above query will not execute properly.Please help. 

View 2 Replies View Related

Count Query Breaking Down By Month

Dec 6, 2013

Need query to expand the count from a month to the entire year in 2013 .Below is my query where I got the values for a month in 2013 ,How do I expand on this query so that it generates for the entire 2013

Query
SELECT RD.RPTDESC,Count(SR.RPTDESC) AS ReportCount,sum(SR.Hrs) as ProdHours,Sum(SR.Mins) as ProdMins, (sum(SR.Hrs)*60+ Sum(SR.Mins)) as TotalProdTime,
(sum(SR.Hrs)*60+ Sum(SR.Mins)/Count(SR.RPTDESC)) as AverageProdTime,sum(SR.TriageHrs) as TriageHours,Sum(SR.TriageMins) as TriageMins,
(sum(SR.TriageHrs)*60+ Sum(SR.TriageMins)) as TotalTriageTime,(sum(SR.TriageHrs)*60+ Sum(SR.TriageMins)/Count(SR.RPTDESC)) as AverageTriageTime

[Code] ....

Results

Jan-13Feb-13
RPTDESCReportCountProdHoursProdMinsTotalProdTimeAverageProdTimeTriageHoursTriageMinsTotalTriageTimeAverageTriage
TimeReportCountProdHoursProdMinsTotalProdTimeAverageProdTimeTriageHoursTriageMinsTotalTriageTimeAverageTriageTime
20506060120252558014014017039394

[Code] ...

Also when I am running for months individually there are certain months where for certain reportids there is no data returned,I would like to populate all zeroes for that row for example report id 306 in February and how to get decimal values in average fields.

View 2 Replies View Related

ASP/SQL Query Build - Myth Breaking

Jul 20, 2005

Hi GuysI don't want to keep asking for your help all the time on each individualquery, so could you please help me to break the myths on the following:1) I have 2 tables. Once is called ACCOUNTS and the other ACCOUNTBUDGET.ACCOUNTS includes all of the usual details for customers and suppliers, egthe account code (as ACCOUNTID), the account name (as NAME), etc.ACCOUNTBUDGET basically holds a transaction line for each month and it'scorresponding year to store the turnover for that month, eg one row containsACCOUNTID, PERIOD (ie the month), YEAR and TURNOVER.Now a lot of the SQL 6.5 tables that I deal with are in this vein and theusual query is that I want to list all of the ACCOUNTIDs and NAMEs thatexist in the ACCOUNTS table and then show for example what their TURNOVER isfor a applicable PERIOD and YEAR, which are all held in the ACCOUNTSBUDGETtable.Now if I do a quick query using MS Query all I get are rows that haverelated values in both the ACCOUNTS and ACCOUNTSBUDGET table when I havespecified say a certain PERIOD and YEAR.The main point of my current reporting problem is that I want to show allthe ACCOUNTIDs and NAMEs in ACCOUNTS that have zero TURNOVER for aparticular PERIOD and YEAR.I'm positive that I have to create a 2 step query/join, but I don't know howto do it. What is the method? People in this NG, can rattle one up inseconds, but I just don't see the logic. Can you help me with this queryand let me know how you manage to fathom it.2) Are there any good web sites that explain in kiddie form how to do thissort of thing?I really appreciate your help on this.RegardsLaphan

View 1 Replies View Related

Sql Query Per Column Number

Sep 8, 2006

is there a way to sql query via a spesific column number

'get from table A row 1 column 1' and then 'get from table A row 2 column 3'

and so on

thanx

View 3 Replies View Related

Column In Query Results To Number Rows Sequentially

Apr 20, 2015

I need a column in my query results that just numbers the rows sequentially (i.e. 1, 2, 3). How can I do that?

View 6 Replies View Related

Parameterized Query That Counts The Number Of Null Values In An Int32 Column

Jul 18, 2006

Using c# in the compact framework, is there a way to do a parameterized query for counting the number of records where a specified column is null. I tried all of these, but none of them work:

cmd.Add(new SqlCeParameter("@IntColumn", SqlInt32.Null));
cmd.CommandText = "select count(*) from TableName where IntColumn is not @IntColumn";

cmd.Add(new SqlCeParameter("@IntColumn", DBNull.Value));

cmd.CommandText = "select count(*) from TableName where IntColumn is not @IntColumn";

cmd.Add(new SqlCeParameter("@IntColumn", SqlInt32.Null));

cmd.CommandText = "select count(*) from TableName where not IntColumn = @IntColumn";

cmd.Add(new SqlCeParameter("@IntColumn", DBNull.Value));


cmd.CommandText = "select count(*) from TableName where not IntColumn = @IntColumn";

cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Int32));
cmd.Parameters["@IntColumn"].Value = SqlInt32.Null;
cmdGetNumRead.CommandText = "select count(*) from TableName where IntColumn is not @IntColumn";

cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Int32));

cmd.Parameters["@IntColumn"].Value = SqlInt32.Null;

cmdGetNumRead.CommandText = "select count(*) from TableName where not IntColumn = @IntColumn";

cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Int32));

cmd.Parameters["@IntColumn"].Value = DBNull.Value;

cmdGetNumRead.CommandText = "select count(*) from TableName where IntColumn is not @IntColumn";



cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Int32));


cmd.Parameters["@IntColumn"].Value = DBNull.Value;


cmdGetNumRead.CommandText = "select count(*) from TableName where not IntColumn = @IntColumn";

cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Variant));


cmd.Parameters["@IntColumn"].Value = SqlInt32.Null;
cmd.CommandText = "select count(*) from Meter where IntColumn is not @IntColumn";

cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Variant));



cmd.Parameters["@IntColumn"].Value = SqlInt32.Null;

cmd.CommandText = "select count(*) from Meter where not IntColumn = @IntColumn";

md.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Variant));



cmd.Parameters["@IntColumn"].Value = DBNull.Value;

cmd.CommandText = "select count(*) from Meter where IntColumn is not @IntColumn";



cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Variant));




cmd.Parameters["@IntColumn"].Value = DBNull.Value;


cmd.CommandText = "select count(*) from Meter where not IntColumn = @IntColumn";

Whenever I use a "is not" in the query I get an exception, otherwise it returns a count of 0 which is not accurate. Any help will be appreciated.

View 5 Replies View Related

Error Number For Duplicate Column Value During An Insert For A Unique Column ?

Jul 27, 2007

I have a table in which a non-primary key column has a unique index on it.
If I am inserting a record into this table with a duplicate column value for the indexed column, then what will be the error number of the error in above scenario? OR How could I find this out?
 

View 2 Replies View Related

Using Column Number Inplace Of Column Name In SQL Select Statement

Jul 20, 2005

Hello All,Is there a way to run sql select statements with column numbers inplace of column names in SQLServer.Current SQL ==> select AddressId,Name,City from AddressIs this possible ==> select 1,2,5 from AddressThanks in Advance,-Sandeep

View 1 Replies View Related

How To Query A Number (street Number)...

Jul 30, 2007

I have a table that has a street number field.
if the user types in a street number of '2' i would like to return all street numbers the begin with 2 (2,20,21, 200, 201,205,2009,...)
how can this be done.

View 10 Replies View Related

Add Symbol To Column Values And Convert Column Values To Western Number System

Feb 12, 2014

I want to add $ symbol to column values and convert the column values to western number system

Column values
Dollar
4255
25454
467834

Expected Output:
$ 4,255
$ 25,454
$ 467,834

My Query:
select ID, MAX(Date) Date, SUM(Cost) Dollars, MAX(Funded) Funding from Application

COST is the int datatype and needs to be changed.

View 2 Replies View Related

Breaking Up A Table

Jul 17, 2007

I've got a Table that has over 500,000 row in it. Now I need to convert the whole thing into Excel to import into another application. So I need to break the table into 10 different tables. How can I do that?

Thanks

View 11 Replies View Related

Breaking Up A Table

Jul 23, 2005

I hope I can get this across clearly.I have a table that needs to be broken into 3 tables.Col1 Col2 Col3 Col4 Col5 Col6 Col7Col1 and Col2 need to go into LookupTable1Col3 and Col4 into LookupTable2If Col5 is twice the width.... haha just kidding...so Col5 and Col6 go into LookupTable3There is a 4th table which is made up of foreign keys which are the PK ofLookupTable1,2,3My questions is, how to get the data from the columns of each row and add itto its respective lookuptableand sequentially step throw the table to repeat the above step until I'veprocessed each rowthanks folksT.B

View 3 Replies View Related

Page Breaking

Nov 2, 2007

What is the best way to page break and I am having trouble. I am building a form based report and need to always break after the last field.

View 3 Replies View Related

Breaking Cluster

Dec 6, 2007

right now we have 2 node cluster . so if i break a cluster it will hamper or not for SQL server. Can you please give me suggestion

View 2 Replies View Related

Breaking The 8kb Barrier On UDT

Mar 15, 2006

Is it possible by any kind of workaround to break the 8kb limit on user-defines datatypes?

My datatype can contain an arbitrary number of double-precision points meaning that I in best case only can store 512 points (2 x 8 x 512). there's a few extra bytes used for something else, but this is roughly the maximum, which is far from what I in many cases need. I serialize the object myself to ensure that I only store what I really need.

View 1 Replies View Related

My Reports Are Page Breaking When I Don't Want Them To.

Dec 20, 2007



Hi, I have a report that is frustrating me. I've built this report, and it is not yet used in production. What it does is page break in places that I don't understand.

The FIRST area it would break after a line that had wrapped text to the line below. Even though there was plenty of room to fit it, and the entire rest of the group on the page.

The second area I honestly have NO idea why it breaks.

View 3 Replies View Related

Column Number

Feb 6, 2008



Hi. Is it possible to select the columns you want given the column number instead of column name like the 3rd and 5th column?

Thanks!

View 3 Replies View Related

Breaking A Row Of Data Into Multiple CSV Rows

Jun 19, 2007

Does anyone have a routine that takes a row of data from database, duplicates/triplicates it, appends some information to it and writes it out as 2/3 CSV rows.

Basically I need to do the following.

Selected from database:
Row1 Col1 Col2 Col3

Output:
Row1 Col1, Col2, Col3, abc
Row1 Col1, Col2, Col3, def
Row1 Col1, Col2, Col3, ghi

Thank you

View 5 Replies View Related

Breaking Down Status Based On Hierarchy?

Jan 23, 2012

I want to show a third-level overview of how many to-do items are in various statuses (pending declined and completed) for the third level individuals.

In this example there's 2 regional leaders (level3), each with local leaders (level2) and individual team members (level1)

Status "1" is a new or pending to-do item
Status "2" is a declined item
Status "3" is a completed item

Here's what I want the output to look like (I may need to add columns for the final report but it should look as follows):

LEVEL3 | Status 1 | Status 1 | Status 2 | Status 3 |
LEADER | TOTAL | 2012 | TOTAL | TOTAL |
-------|----------|----------|----------|----------|
paul | 3 | 1 | 1 | 0 |
roger | 1 | 1 | 1 | 0 |

Here's some sample data similar to my live DB:

CREATE TABLE #items (id int, datestamp datetime, userid int, status int)
INSERT INTO #items (id,datestamp,userid,status) VALUES (1,'12/26/2011',1,1)
INSERT INTO #items (id,datestamp,userid,status) VALUES (2,'12/26/2011',2,1)
INSERT INTO #items (id,datestamp,userid,status) VALUES (3,'1/2/2012',4,1)
INSERT INTO #items (id,datestamp,userid,status) VALUES (4,'1/3/2012',1,2)

[Code] ....

I have a view called hierarchy that shows me the hierarchy for each level 1 user. Here's the source for the view:

SELECT
a.id 'level1',
b.id 'level2',
c.id 'level3'
FROM #users a
INNER JOIN #users b ON a.reportsto = b.id
INNER JOIN #users c ON b.reportsto = c.id

I've run into table/view limits when using a straight query and timeouts when trying to create a cursor that looks through all level3 users and populates a temporary table with an INSERT and multiple UPDATE statements to get the individual counts for each column for each user.

(Just a note on the actual database in case it's relevant: It has about 1000 LEVEL1 users, 50 Level2, and 15 Level 3 users. It will generate about 1400 to-do items per week.)

View 4 Replies View Related

Parameter For Breaking At Start Of Group

Jan 30, 2006

Hello,

I'm using SQLServer 2005 and I build a report in the visual studio.

I need a way to pass as parameter whether to break at start of a specific group or not.

Thanks, Talia.



View 5 Replies View Related

Chart Formatting And Non Breaking Spaces

Sep 20, 2006

Hi all,

I'm having a problem and I cannot seem to find how to fix this anywhere on the web.

I have a stacked bar chart with my data labels showing down the x axis. My client wants me to put a bit of white space between the label and the actual chart. So I tried adding spaces to my data label but of course RS thinks they don't exist since they are not non breaking spaces.

How can I do the following in my chart label

=Fields!Myfield.Value & " "

As you can see I want to add a couple of spaces to my data label. I can't use &nbsp;, all the ampersands get confused. I tried & char(32) but RS doesn't like char(n). I tried ChrW(160) [I saw this in the web somewhere] but this didn't work either.

Is there anything I can do?



thanks in advance, I'm at my wits end (again)

Annabel

View 2 Replies View Related

Column With A Number And 2 Decimal Like 10.10 Or 150.30

Nov 15, 2006

Hello,How should I create a column to save data with the folowing format   10.10 or 10.20 or 10.30 or 150.30  or 10 or 150.It is basically  process step in a diagram flow.I tried with decimal but with 10.10 , it removes automatically the 0.Thanks

View 2 Replies View Related

Auto Number Column

May 29, 2008

hi
I am using sql Server as database what value should I send for the autonumber field
Thanks 

View 1 Replies View Related

Can You Select A Column By Number Rather Than Name?

Jun 23, 2004

I swear I have done this before and can't remember how.

I want to select columns in a SELECT statement by their number instead of name.

I have looked everywhere for the syntax.

Also, is there a function to 'count' the number of columns in a table?

Thanks.

View 2 Replies View Related

How To SELECT By Column Number ?

Dec 7, 2004

I want to select with column order without field name .
Is it possible ?

SQL2000 db
...

I want to select a field by its order in table, but without naming it to have a constant name of it, like :

select (column01) as L1 from myTable

View 6 Replies View Related

Addition Of A Number To An INT Column

Dec 1, 2007

Hi,

I have several INT columns in a table that I need to update.

For example, in column 'aa' I need to add 2 to all of the values in that column.

I'm using Query Analyzer - what update statement should I write?

View 3 Replies View Related

Creating Number Column

May 14, 2008

I have a ACTION column. i.e Its only disply SELL/BUY.

ACTION

BUY
BUY
BUY
SELL
SELL
SELL
SELL
BUY
BUY
SELL
SELL
SELL
BUY

How to create another column display like this

ACTION

BUY 1
BUY 2
BUY 3
SELL 1
SELL 2
SELL 3
SELL 4
BUY 1
BUY 2
SELL 1
SELL 2
SELL 3
BUY 1

Help me anyone...

View 9 Replies View Related

Creating Number Column

May 14, 2008

I have a ACTION column. i.e Its only disply SELL/BUY.

ACTION

BUY
BUY
BUY
SELL
SELL
SELL
SELL
BUY
BUY
SELL
SELL
SELL
BUY

How to create another column display like this

ACTION

BUY 1
BUY 2
BUY 3
SELL 1
SELL 2
SELL 3
SELL 4
BUY 1
BUY 2
SELL 1
SELL 2
SELL 3
BUY 1

Help me anyone...

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved