MS SQL Server :: How To Split A Field Into Two Fields

How To Split A Field Into Two Fields

Apr 11, 2006

I have the following fields in table A:

GL_ID| Date |GL_Name_VC | Amount |Period_TI|Year_SI
===============================================================
1000|31/12/2005 | Sales | -8,000.00 | 12 | 2005
===============================================================
1000|06/01/2006 | Sales | -6,000.00 | 01 | 2006
===============================================================
1000|20/01/2006 | Sales | 2,000.00 | 01 | 2006
===============================================================
1000|28/01/2006 | Sales | -4,000.00 | 01 | 2006

The above database is running on Microsoft SQL Server 2000 and i would like to query
for a report that looks something as below:

Period | Date | GL_Name_VC | Debit | Credit| Net Change | Balance
=====================================================================
01 |01/01/2006|Opening Bal | 0 | 0 | 0 | 8,000
01 |06/01/2006|Sales | 0 | 6,000 | 0 | 0
01 |20/01/2006|Sales | 2,000 | 0 | 0 | 0
01 |28/01/2006|Sales | 0 | 4,000 | 8,000 |16,000

The formula for the above calculated fields are as below:

Opening Balance = carried forward balance from Year 2005
Debit = All positive amount
Credit = All negative amount
Net Change = Total Credit - Total Debit in Period 01
Balance = Total of Net Change + Opening Bal

Guys, hope someone out there can help me with the sql command for the above report?

View 2 Replies View Related

UNION With A Max On Split Date/Time Fields

Mar 30, 2000

I have 2 tables, each with one ID field, a separate
Date and Time fields and a number of other fields.
The tables contain duplicates on the ID field.
I want to do a UNION keeping only the record with the latest
Date and Time.

This would work:
SELECT MyTab.myKeyField, Max(MyTab.myDate) AS myDate
FROM (SELECT myKeyField, myDate
from Table1
union
SELECT myKeyField, myDate
from Table2) AS MyTab
GROUP BY MyTab.myKeyField

But is only taking care of Date, not Time (some records have
the same date but different times)
The other problem is, when I add more fields, I have to
include them in the GROUP BY clause, and this way I end up
with duplicates (because some other fields have different
values)

Is there a way to do this?

View 1 Replies View Related

Split Fields And Display Query Result

Mar 21, 2007

Hi,
I'm having a problem in spliting the fields
I need to ru the following query to join two tables and getting the output as shown.

Query:
select cusl.user_name,
pmts.bill_ref_info, pmts.payee_acid, pmts.cust_acid, pmts.txn_amt,pmts.pmt_id
from cusl, pmts
where cusl.ubp_user_id = pmts.ubp_user_id and pmts.ubp_user_id= 'testinglive'

Output:
user_name bills_ref_info payee_acid cust_acid txn_amt
SAMEER ALLA0210181#123456#Amita 378902010021095 383702070051411 1.000 16318
SAMEER BARB0GNFCOM#6788990#Vikram Kalsan 378902010021095 383702070051411 1.000 16327
SAMEER BKID0000200#378902010099678#Vikram 378902010021095 383702070051411 1.000 14031
SAMEER undefined#123456789123456#Vikram 378902010021095 383702070051411 1.000 13918

Now I need to display the second field which is a #-separated field as individual fields alongwith tghe other fields that are shown on execution of the query.
Can this be done? Please guide me on this...

View 7 Replies View Related

How Can I Split Fields And Depending One Column Decide The Foreing Key

Oct 19, 2007

I�m wondering how to solve the following scenario with SSIS

I have a CITY table and a STATE table, I have to load a file with the information regarding to the CITY:

the state table is like this:

StateCode(PK) stateLegalCode stateName
============= ============== =========
1 01 Florida

the city table is like this:

citycode(PK) cityLegalCode cityname StateCode(FK)
============ ============= ======== =============
1 1001 Quakertown 1

the file has the following information

cityLegalCode cityName
============= ========
01-1001 Quakertown
...

how can I load the file into CITY table:

1-) with the file's cityLegalCode I have to split the string and if the two initial digits are 01 the registry must have 1 in the StateCode(FK).

how can I do something like that using SSIS???

thanks

View 3 Replies View Related

How Does Full Name Field Split To First Name And Last Name Field?

Oct 9, 2001

I have a table with column person_name and I want to update that table and
insert the values of different columns FIRST_NAME and LAST_NAME.

Anybody can tell me the T-SQL code to do above problem?

Thank you very much!

View 3 Replies View Related

Integration Services :: SSIS Package To Split Address Into 3 Fields

Jun 10, 2015

I have a table that I am using in a package to create an extract from. In that table is an address field called "Street" that is 255 characters in length. My table also has 3 additional fields called address_1, address_2 and address_3 that are each 50 characters in length because that is the requirement for my extract. I need to split the address field up in such a way that if it is longer than 50 characters, it backs up to the first space in the address prior to character #50, puts that info in street1, then from that cut off point used in street1, puts the next 50 up to the prior blank space in street2, then the remainder in street3. Where the extract will be used only has three 50 character fields so if the data runs more than 150 characters, the street3 data will just have to be truncated. No way around that, but I don't anticipate any address getting close to that long. Although doing such a split would be much easier using SQL, the solution requirement is that it be done in the package, not using SQL to do so.

I'm assuming I need to use a "derived column transformation" in my data flow. But, I can't figure out how to do what I need to do with a derived column transformation.

Example of info in an address:
123 Chicamauga Avenue South, Across the Street from International Center Square, Apartment Number 17650 Tokiwa-machi Machida

position 1-50:
123 Chicamauga Avenue South, Across the Street fro

Therefore, Street1 would need to get:
123 Chicamauga Avenue South, Across the Street

[code]....

View 5 Replies View Related

Separating One Field Into Two Fields Based On A Character In The Field

Jul 20, 2005

I know there has to be a way to do this, but I've gone brain dead. Thescenario..a varchar field in a table contains a date range (i.e. June 1,2004 - June 15, 2004 or September 1, 2004 - September 30, 2004 or...). Theusers have decided thats a bad way to do this (!) so they want to split thatfield into two new fields. Everything before the space/dash ( -) goes intoa 'FromDate' field, everything after the dash/space goes into the 'ToDate'field. I've played around with STRING commands, but haven't stumbled on ityet. Any help at all would be appreciated! DTS?

View 1 Replies View Related

Split A Field

Jul 23, 2005

Whats the best way to do the following?Field1 in Table1 contains numbers and characters seperated by #Examples: aaa#01, kjhkjhjh#21 and jlkjlkj#123How can I create two new fields in Table1, one containing what is tothe left of the # and the other what is to the right?Regards,Ciar�n

View 4 Replies View Related

How To Split The Field In The Sql Query?

Mar 23, 2007

    I have the field LocationID (string)which has values like
          "AZ001","AZ002","IN002","IN004" first 2 will be always alphabets and remaining 3 will be numbers,
I want to split it like "AZ" ," 001"
                              "AZ","002"
                              "IN" "002"
                              "IN" "004"
now i will populate dropdownlist with unique values of "AZ" "IN"
according to first dropdownlist how i will populate second dropdownlist?
So how to write sql query for splitting? and then populating the dropdownlist ?

View 4 Replies View Related

Split Up Comma-delimited Field

Jun 7, 2002

I have a row in a SQL table that has 4 numerical values, separated by comma. I'd like to take this and make it 4 separate columns. Values are not always the same length, but are always delimited by commas.

Any ideas how I could do this in T-SQL?

View 9 Replies View Related

Can I Split A Field Based On A Character?

Nov 9, 2005

Here's a question for the SQL gurus out there:
I have a varchar(20) field DIAGNOSISCODE in a table that can either be null, or contain up to 3 comma-separated codes, each of which relates to a description in another table. For example, some sample rows might be
8060
8060,4450
8060,4123,3245
Now I need to structure a query to return these values from this single field as three fields CODE1, CODE2, CODE3, with NULL as appropriate for example
CODE1=8060, CODE2=4450, CODE3=NULL.
I have been using CASE along with CHARINDEX and PATINDEX but it it becoming extremely messy. Can anyone think of a "neater" way to return three fields from this one field?
Any help very greatly appreciated.
Thanks, Simon.

View 5 Replies View Related

Split Address Into Multiple Field

Aug 3, 2006

i want to write a code that can split the addresses into multiple fields. anyone can point me out where to start?

lets say i have "12 north plaza boulevard apt.16" and i want it to become:

address_number
12

suffixA
North

street_name
plaza blvd

suffixb
apt16

View 1 Replies View Related

Split One Field In Multiple New Rows

Nov 27, 2007

HiHo,
just a beginners question:

I have the following row with 2 fields:

Field 1: Task A
Field 2:� 1;2;3;4

The number of semicolon divided elements in Field 2 is variabel.

I would like to create new rows like:

Row 1 Field 1: A Field 2: 1
Row 2 Field 1: A Field 2: 2
Row 3 Field 1: A Field 2: 3
Row 4 Field 1: A Field 2: 4

I think I should use a Foreach Loop.
But I don't exactly how to do it?

best regards
Chris

View 5 Replies View Related

Conditional Split On Field In Csv File

Jun 21, 2007

I know this should be simple but I can't figure it out. I am reading in a csv file to a conditional split task, all I want to do is split the file based on a field. Some values in field will have a suffix say ABCD while others wont. So my conditional split says Right(FieldA,4)=="ABCD" which then splits file in two directions or at least it's meant to. Problem is that it does not work. I think it has something to do with the field type in the csv file although I have tried using a Data Conversion task but to no avail all the field values with ABCD suffix are ignored by my conditional split and head off the same way as other values. Funny thing is is that if I manually add a value to the file with a suffix of ABCD and run task again then the conditional split works on the manually added row and all rows with suffix of ABCD. It's like it does not recognise previous values as string until one is added manually.

Thanks

View 8 Replies View Related

One For The SQL Gurus: Split A Delimited Field Into Rows

Aug 13, 2004

Hi.

I'm trying to write an SQL Query that will take a delimited field and return each item as a row.

Example

Take the AuthorizedRoles and TabID fields from the Tabs table

AuthorizedRoles TabID
0;11;__________1
0; 15 ;17;______6
-2;____________7

I would like to return a unique record for each Authorized Role

AuthorizedRole TabID
0____________1
11___________1
0____________6
15___________6
17___________6
-2___________7

Any ideas?

Cheers
Dave

View 2 Replies View Related

Split Text Field - Display 1st Or 2nd Word

Sep 24, 2012

In SQL SErver 2008, I have a text column. I need to display either 2nd word in the text column or 1st word in the text column based on certain conditions.

How shall i display either 2nd word or 1st word from a text field.

View 1 Replies View Related

Transact SQL :: DATALength - Split A Field Based Off Its Length

May 15, 2015

I need to split a field based off it's length. Initial thought was to take Left(field, characters), and Right(field, characters), but now I see I am duplicating the data as I am capturing too much of the data in. However, what I actually need to achieve is in field1 capture the left 1000 characters, and in field 2 take from character 1001 to the end. How can I update my syntax so that field2 will ONLY capture from character 1001 to end?

field1 = COALESCE(CASE WHEN DATALENGTH(exitinterviewnotes) > 998 THEN LEFT(CAST(exitinterviewnotes AS VARCHAR(MAX)), 1000) ELSE exitinterviewnotes END,''),
field2 = COALESCE(CASE WHEN DATALENGTH(exitinterviewnotes) > 998 THEN RIGHT(CAST(exitinterviewnotes AS VARCHAR(MAX)), 1000) END,'')

View 5 Replies View Related

Reporting Services :: Display Check Box In SSRS Within Database Field Split By A Separator?

Aug 12, 2015

I have a database field which consists of a long string of values with some separator.

Ex: Injection^!@$#Medication

in my report i have to split the string as

Injection
Medication

till here i am able to display the results but in addition to that i have to display check box against these values like below

and this string can contain any no of values ex: if we have 4 values are separated within a string then i have to display 4 check boxes against 4 results.

View 6 Replies View Related

SQL Server 2014 :: Split Out A Field Of Comma Separated Values Based On Unique Code In Same Row?

Oct 21, 2014

I have a comma separated field containing numerous 2 digit numbers that I would like splitting out by a corresponding unique code held in another field on the same row.

E.g

Unique Code Comma Separated Field

14587934 1,5,17,18,19,40,51,62,70

6998468 10,45,62,18,19

79585264 1,5,18

These needs to be in column format or held in an array to be used as conditional criteria.

Unique Code Comma Separated Value

79585264 1

79585264 5

79585264 18

View 5 Replies View Related

Break One Field Into 3 Fields

Aug 27, 2003

Hello all,

I have a problem. I have the address like following:

204 east 40th street,brooklyn,ny 11229

I have to break this field into four fields:
address,city,state,zip

address should be: 204 east 40th street
city should be: brooklyn
state should be: ny and
zip should be: 11229

How would I be able to do that?

Any help will be appreciated.

Thanks in advance!!

View 3 Replies View Related

How To Divide A Field Into More Fields?

Apr 7, 2008

Hello,

I have a field like below which has blanks 'b';

CODE
000123'b'99999'b'RED RING

WHAT i want to do is like below?

FIELD1
000123

FIELD2
99999

FIELD3
RED RING

View 1 Replies View Related

Header In Fields With Field Value

Jan 29, 2015

I have a table returning results like this

Row1 || Row2 || ERPID || ParentID || LevelID || Category || SubCategory || DDate || publish
1 || 1 || 10152159 || 1015 || 2159 || LOCTITE || LOCTITE1 || 29/01/2015 12:10 || 0
1 || 2 || 10152134 || 1015 || 2134 || LOCTITE || LOCTITE2 || 29/01/2015 12:10 || 0
1 || 3 || 10152157 || 1015 || 2157 || LOCTITE || LOCTITE3 || 29/01/2015 12:10 || 0
2 || 1 || 10062003 || 1006 || 2003 || COMPUTER || COMPUTER1 || 29/01/2015 12:10 || 1
2 || 2 || 10062148 || 1006 || 2148 || COMPUTER || COMPUTER2 || 29/01/2015 12:10 || 1

[Code] ....

How I can make the result return like this way?

Row1 || Row2 || ERPID || ParentID || LevelID || Category || SubCategory || DDate || publish
1 || 1 || 10151015 || 1015 || 1015 || LOCTITE || || 29/01/2015 12:10 || 0
1 || 1 || 10152159 || 1015 || 2159 || LOCTITE || LOCTITE1 || 29/01/2015 12:10 || 0
1 || 2 || 10152134 || 1015 || 2134 || LOCTITE || LOCTITE2 || 29/01/2015 12:10 || 0
1 || 3 || 10152157 || 1015 || 2157 || LOCTITE || LOCTITE3 || 29/01/2015 12:10 || 0
2 || 2 || 10061006 || 1006 || 1006 || COMPUTER || || 29/01/2015 12:10 || 1
2 || 1 || 10062003 || 1006 || 2003 || COMPUTER || COMPUTER1 || 29/01/2015 12:10 || 1

[Code] ....

Here is the code I use

SELECT
DENSE_RANK() OVER (ORDER BY clroot.Ctgry1.Descr ASC) AS Row1, Row_Number() OVER (partition BY clroot.Ctgry1.Descr
ORDER BY clroot.Ctgry1.Descr, T1.Descr ASC) AS Row2, left(t1.ID,4)+right(t1.levelid,4) AS ERPID,T1.ID AS Ctgry1ID, clroot.Ctgry1.ID AS ParentID, T1.LevelID, clroot.Ctgry1.Descr AS Category, T1.Descr AS SubCategory,

[Code] .....

View 9 Replies View Related

Two Int Fields Or One Varchar Field

Jul 20, 2005

I am setting up a database that will receive a lot of data from twoseparate telephone centers, the log table will in a short time haveover 1 million lines, and I was wondering if I should use 1 identifyfield or two:case 1:[Id] [int] IDENTITY (1, 1) NOT NULL[ServerId] [int] NOT NULLcase 2:[Id] [varchar(20)] IDENTITY NOT NULLWhere in case 1 I would just use a combination of Id and ServerId toidentify the line, where in case 2 I would have the Id field a varcharthat would look something like A-000001, A-000002 for server 1 andB-000001, B-000002 for server 2Which solution will be faster when searching for a record when thewill have over 1 million lines?

View 3 Replies View Related

Same Field From Several To Seperate Fields Of A Single Row

Nov 11, 2007

Hi,

I'm struggling with this. I'd like to perform a joined query
from two or more tables and take the same field from several
rows of one table into seperate fields of a single row in a new table.

Like this:

table 1

uidunameuloc
1mehere
1methere
2youhere
2 youthere

table 2

uidulocuparam#uparamval
1here1a
1here2b
1here3c
1there1d
1there2e
1there3f
2here1g
2here2h
2here3i
2there1j
2there2k
2there3l

result in table 3

uidunameuloc uparval1 uparval2 uparval3
1mehere a b c
1methere d e f
2youhere g h i
2 youthere j k l

uparam# field in table 2 always the same sequence

Any ideas????

Thanks...

View 2 Replies View Related

Updating A Field With Info From Other Fields

May 8, 2008

I am trying to make a field that has info from othe fields

Table = Page0
Fields = LName, FName, SS

I want a new field (Folder) to be all three fields.. for example
LName= Smith
FName= John
SS= 1234

I want to update Folder = Smith_John_1234

View 5 Replies View Related

Combine Column Fields Together Into One Field

Jan 24, 2007

Hello to All,

I needs help to combine these together but how would I eliminate necessarily zero in front of "PropertyHouseNumber".

Table: DirectHome

Column fields.......
PropertyHouseNumber, PropertyStreetDirection, PropertyStreetName, PropertyMODE

0000001091 , W , 000026TH , RD

Thank you

RV

View 5 Replies View Related

Display All Fields Including The Field Has Dup

Nov 14, 2007

I have one table where i want to use aggregate function for duplicate rows and also be able to select all fields to display. How would i do that?

Here is my query:

select Z, count(*)as num from Table
group by Z
having count(Z) > 1 ----- this returns 213 rows

Select Z, A,B,C,D From Table
Group By Z, A,B,C,D
Having Count(Z)>1 ----This gives me nothing

Hope I am explaining this correctly as to what I want.

Thanks,
Saru Brochu

View 3 Replies View Related

Non-existing Field In Fields Collection

Apr 11, 2007

I'm developing a report in RS 2000 that uses two datasets. The first dataset provides data to a drill-down report on the top part of the screen and the second small dataset provides some data for additional summary information on the bottom of the screen. I'm getting a runtime error "An unexpected error occurred in Report Processing. The expression referenced a non-existing field in the fields collection." Of course, I've edited every expression I can think of: text boxes, data grid, parameters, filters, drill-down lists... I think I've gone through every object on the report but I can't isolate the bad expression. Can anyone suggest a better way to debug this type of error? It compiles fine, but fails in runtime. thanks.

View 1 Replies View Related

Joining Two Fields To Single Field

Jun 14, 2006

If I have a database table with the following columns:
ID
Other_ID
Description

And I want to join the two ID fields to one field in another table that contains the following fields:
ID
Name

How would i do that?

Here is some sample data and what I would like returned

TABLE1

ID Other_ID Description
row 1 1 2 Number1
row 2 3 1 Number2

TABLE2

ID Name
row 1 1 John
row 2 2 Bob
row 3 3 Bill

I want to query TABLE1, row 1 so that I pull back the Names for the values stored in the ID and Other_ID fields so that my results are like:
John Bob Number1

The only way around it now is that I store Other_Name in Table1.

Thanks.

View 4 Replies View Related

Update Field Based Upon Other Fields

Jan 24, 2008

Hi,

I have a table with eight (8) fields, including the primary key (rfpid). Three of the fields are foreign keys, which take their values form lookup tables. They are int fields (pmid, sectorid, officeid). One of the fields in this table is based on putting together the descriptive field in the lookup table for sector (tblsector). The two other fields to be part of this string are the rfpname and rfpid. This creates the following string:

rfpsector_nameproposalscurrent_year
fpname_08_rfpid

NOTE:

The words rfp, proposals are words that have to be part of string;

the slashes are to also appear.

current_year would be defaulting to datepart = year (2008)

The part that has the last two digits of the current year then the underscore and then the rfpid should be connected by an underscore to the rfpname.
I am at a loss and would greatly appreciate any help.

Thanks

View 51 Replies View Related

Select Fields Based On The Value In A Field

Sep 2, 2015

I have a table with two sets of fields, one for PRIMARY Mail Address and the other for ALTERNATE Mail Address. Both sets are five fields long. I will call them PRIMARY & ALTERNATE for this discussion. I want to select the PRIMARY fields if ALTERNATE is either null or zero, and the ALTERNATE fields if data is present.

I have used this syntax but it gives a table with both the PRIMARY & SECONDARY records.

SELECT   TXPRCL, TXALTR, TXANAM
FROM   dbo.PCWEBF21
WHERE   (TXALTR > 0)
UNION
SELECT   TXPRCL, TXTAXP, TXTNAM
FROM   dbo.PCWEBF21
WHERE   TXALTR = 0

Would a Select Case work? something like

Select PRIMARY when ALTERNATE = 0, and ALTERNATE when ALTERNATE > 0 or is not null.

View 17 Replies View Related

Join Several Fields To Create New Field

Oct 4, 2006

What would be the recommendation/approach in creating a seperate field in which joins several differate fields together.

I have a table with field name a, b, and c. I want the information in those fields to be populated in a seperate field, d.

So instead of:

a

122

b

joe

c

st

I would have:

d

122 joe st

Thanks!

View 12 Replies View Related