SQL Server 2014 :: Splitting Similar Data Into Separate Columns?

Aug 18, 2015

If you see below there are 2 customer names on 1 loan, most of them share the same lastname and address, I want to separate it with fields,LoanID, customer 1 Firstname, Customer 1 Lastname, Customer 2 FirstName, Customer 2 Lastname, Adddress,zip

Loan IDFirst NameLastnameAddressaddress 2CityStateZip
1236048Joey Yesen xxxx abc GROVE RDNULLCLEVELANDTX77327
1236048Dickey Yesen xxxx abc GROVE RDNULLCLEVELANDTX77327
1235983Randy Seany xxxx abc Haleyville StNULLAuroraCO80018
1235983Barry Seanyxxxx abc Haleyville StNULLAuroraCO80018

The query I am using

select
L.Loanid
,B.FirstMiddleName
,B.LastName
,MA.AddressLine1
,MA.AddressLine2
,MA.City
,MA.State
,MA.Zip

from Loan AS L

LEFT JOIN Status As S on S.LoanID = L.LoanID
LEFT JOIN Borrower B on B.LoanID = L.LoanID
LEFT JOIN MailingAddress MA on MA.LoanID = L.LoanID
where S.PrimStat = '1' and B.Deceased = '0'

View 3 Replies


ADVERTISEMENT

Splitting One Data Field With Hyphens To Separate Records

Feb 16, 2004

I receive a file that will have hyphens between data items such as
123-aed-edr-45r-ui9
1-ed3-45r-rrr-98u
I need to split the values to load into a table that will hold the 5 separate data itens. The fields will always have the hyphens but could be different lengths. Any idea on a best approach to split this in tsql

View 2 Replies View Related

SQL Server 2012 :: Splitting Data Between Two Columns

Dec 15, 2014

Here's the resultset:

Main
CC-09-00032-D
CC-09-00113-A
PR-10-01004-2

Expected result:

P C
PR-10-01004-2 CC-09-00032-D
CC-09-00113-A

What I need is split the data into two columns if data in column Main starts with 'PR-' then output result to column P and if it starts with 'CC-' then to column C (the output needs to be in one table).

View 3 Replies View Related

SQL Server 2012 :: Extracting Data From Row And Inserting Into Separate Columns In Different Tables?

Mar 19, 2015

From my query I am getting results like below in one of the column:

'immediate due 14,289.00
04/15/15 5,213.00
05/15/15 5,213.00
06/15/15 5,213.00
07/15/15 5,213.00
08/15/15 5,213.00
09/15/15 5,213.00
10/15/15 5,213.00
11/15/15 5,210.00'

this same type of many rows are there (i just mentioned one) but having same pattern with tabs as delimiter in between dates and amount.

I need something that shows Date on one side representing particular amount on the other

For Immediate Due it will be current date and the amount besides it.

how can I achieve this.

View 8 Replies View Related

SQL Server Admin 2014 :: Separate Data Files / Log Files / TempDB / Backups

Jan 9, 2015

I proposed on a new server that we separate Data Files, Log Files, tempDB, Backups, etc. onto separate LUNS on a SAN with High Speed Solid State Drives.I was told that with the new technology with solid state SAN's that it would decrease performance and that it did not work the same way as it did when you had RAID 5's etc.I thought that if things were cared out correctly by a SAN Administrator they would know how to configure for optimal performance.

View 2 Replies View Related

SQL Server 2012 :: Select Query To XLS Output - Export Data In Columns To Separate Tabs In Excel

Apr 21, 2015

Using below script to export the select statement result to .xls

declare @sql varchar(8000)
select @sql = 'bcp "select * from Databases..Table" queryout c:bcpTom.xls -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql

But result is not exporting in seperate tabs, all 4 column details are exporting in single cell.

how to export the data in columns to separate tabs in excel.

View 2 Replies View Related

Splitting One Column Data Into Number Of Columns

Nov 20, 2014

I need splitting one column data into number of columns.

Name. Below are the sample of names

Doxycycline 200 mg capsule
Effexor 100 mg tablet
Duragesic 50 mcg/hr Transderm Patch
Ambien CR 6.25 mg Tab

My desired results are below..

name dosemeasmisc
Doxcycline200mgcapsule
Effexor 100mgtablet
Duragesic 50mcgTransderm Patch
Ambien CR 6.25mgTab

View 1 Replies View Related

SQL Server 2014 :: Merge Similar Records From Excel?

May 19, 2015

I am working on a project that will require me to get a flat data file (excel spreadsheet) with hundreds of thousands of records. Each record is an Owner, and specifically what they own. There will be a field for OwnerName that I want to figure out a way to pull the data into a database like;

Table(Owners) - make sure owner is listed only once

Table(Properties) - joined to owners showing all properties that person owns

Now the tricky part, the owner names might not be exactly the same. Some records might have;

Smith, John
John Smith
Smith, John T
etc.

To make matters worse, this will be a continuous process. I will receive updated excel spreadsheets from time to time and will need to import the new records, many times overwriting the old data. For the good news, there should be an OwnerID that will be unique within the excel data. So as I am merging similar records into the Owners table, I should have a list of OwnerID's that can forever be used to link to the owner.

View 3 Replies View Related

SQL Server 2014 :: Similar Queries Produce Different Output?

Sep 22, 2015

create table #t1 (id int)
create table #t2 (id int)

insert into #t1 values (1)
insert into #t1 values (2)
insert into #t1 values (3)

insert into #t2 values (1)
insert into #t2 values (2)

Run the below quires, you will get 2 different outputs.Second is the desired output. I cant find reason for query1

-- Query1

select * from #t1 a left join #t2 b on a.id = b.id and b.id is null

-- Query1

select * from #t1 a left join #t2 b on a.id = b.id where b.id is null

View 3 Replies View Related

Data Mining :: Merge Two Or More Columns In Separate Tables

Jul 14, 2015

SQL 2008R2

Request is to merge or join or case stmt or union or... from up to four unique columns allĀ in separate tables to new combined table (matrix) of results from said.

What is example of best method to do this ?

View 2 Replies View Related

SQL Server Admin 2014 :: Separate Max Fields From The Others To Another Filegroup?

Jan 18, 2015

we have many max fields in our database .

A role in performance says : It's better to separate this fields to another FileGroup with separate file.

Is the result good for performance? and what are the risks?

View 9 Replies View Related

SQL Server Admin 2014 :: Separate One Availability Group To Several AVs?

Jul 3, 2015

We have 4 servers : Server 1 , Server 2 and HA , DR servers.

I designed 2 Plans to get HA support for my databases .

Which of them are better , And is there any problem in my design ?

View 4 Replies View Related

SQL Server 2014 :: Import Data From Excel To Table - Null Instead Of 0 In Columns

Oct 29, 2013

I have a excel sheet with some data and blank columns. I have a ssis package using to import data from excel to sql table. For blank excel columns it is importing as null instead i want to show them as '0'. If data comes in it should update the data.

View 8 Replies View Related

Add 2 Separate Columns From Separate Tables Using Trigger

Feb 15, 2012

I am trying to add 2 separate columns from separate tables i.e column1 should be added to column 2 when inserted and I want to use a trigger but i don't know the syntax to use...

View 14 Replies View Related

SQL Server Admin 2014 :: Reporting Services On Separate Box From Database

Sep 8, 2014

I've got reporting services on a different box from the database and I can see all the reports, but when I try to setup a subscription, I get this weird error:

The SQL Agent service is not running. This operation requires the SQL Agent service. (rsSchedulerNotResponding)

The same error happens when I connect to the database server via management studio and try to run a job.

I can confirm that SQL Agent service is running.

View 1 Replies View Related

SQL Server 2014 :: Separate Words To Rows (Convert Comma Separated)

May 3, 2015

I have a table Sample with data stored like below

ID|STRING |
------------------------------------------------------------------
1| 'ENGLAN SPAIN' ITALY 'FRANCE GERMANY' BRAZIL

I need the output like..

-----------------
|ENGLAND SPAIN |
|---------------|
|ITALY |
|---------------|
|FRANCE GERMANY |
|---------------|
|BRAZIL|
-----------------

How can I do the same with a select query in SQL Server?

View 4 Replies View Related

SQL Server Admin 2014 :: Separate Transaction Log Files For Multiple Databases?

May 15, 2015

We have multiple databases on a single instance in an OLTP environment. I have my data files on a separate SAN LUN from my transaction log files (and a few NDFs split out onto additional LUNs). I was wondering if there is a performance benefit to putting each LDF file on its own LUN? Or at least my few busiest LDFs?

We are currently on 2012, but I'm having to put together specs for a 2014 installation and need to answer this question without having an environment in which I can benchmark different setups. I just want to hear whether or not others have done this (why or why not?).

View 3 Replies View Related

SQL Server 2012 :: Splitting A Single Column Into Multiple Columns?

Mar 3, 2015

I have a description field in a table which also stores unit of measure in the same column but with some space between them, I need to split these into two different columns.

For Eg:

Description
APPEARANCE UNIT
BDV KV
DENSITY KG/L

View 9 Replies View Related

SQL Server 2012 :: Splitting Column Values In Multiple Columns And Assigning It To Row

Dec 11, 2013

How do I write a query using the split function for the following requirement.I have a table in the following way

Identity Name Col1 Col2 Col3
1 Test1 1,2,3 200,300,400 3,4,6
2 Test2 3,4,5 300,455,600 2,3,8

I want an output in the following format

Identity Name Col1 Col2 Col3
1 Test1 1 200 3
1 Test1 2 300 4
1 Test1 3 400 6
2 Test2 3 300 2
2 Test2 4 455 3
2 Test2 5 600 8

If you see the data, first element in col1 is matched to first element in col2 and 3 after splitting the string.

View 2 Replies View Related

SQL Server 2014 :: Creating A Table With Updatable Columns And Read-only Columns

May 26, 2015

Here is My requirement, I'm not sure if this is possible. Creating table called master like col1, col2 col3, col4 , col5 ...Where Col1, col2 are updatable - this can be done easily

Col3, col4 are columns in another table but these can be just a read only ?? Is this possible ? this is possible with View but not friendly with share point CRUD...Col 5 is a computed column of col 2 and col5 ? if above step can be done then sure this can be done I guess.

View 4 Replies View Related

SQL Server 2014 :: Get SUM Of The Sum Of Three Columns And All Three Columns Have Nulls?

Jul 13, 2015

Basically I need to get the SUM of the sum of three columns and all three columns have nulls. To make it more complicated, the result set must return the top 20 in order desc as well.

I keep facing different issues whether I try and use Coalesce, IsNull, Sum, count, anything. My query never returns anything but 0 or NULL regardless of if I am trying to build a CTE or just use a query.

So I'm using Col A to get the TOP 20 in order (which is fine) but also trying to add together the sums of Col A + Col B + Col C for each of the twenty rows...

View 2 Replies View Related

Find Out Similar Columns

Apr 19, 2007

i like to write a sql query to find out similar columns in different tables.

i have something like this. but my logic is wrong.

select *
from information_schema.columns a
join information_schema.columns b
on a.column_name = b.column_name
and a.table_name <> b.table_name

View 1 Replies View Related

Splitting Up One String Into Three Columns

Aug 14, 2003

I'm trying to split a hyphen-delimited string into three columns in a view. I've been using substring and len to split up the string, but it is getting very complicated (and isn't working in all cases). I've used a SPLIT function in vbscript - does t-sql have anything similar? I've attached a spreadsheet that shows what I am looking for. Maybe someone can guide me in the right direction?

Thanks.

View 3 Replies View Related

Splitting Up Columns Mid-process

Jun 7, 2007

When I accept my input data, it's convenient to read it in from a flat file as a fixed width file with a width of 100. Then I run a splitter to split the input into two different paths (path_1 and path_2, say), based on an indicator field.

The output of path_1 has 1 column with width of 100, but now I want to split it up into lots of little columns. The output of path_2 also has 1 column with a width of 100, but I want to split it up, too, only using a different fixed width map than i used for path_1.

Any ideas?

Thanks!

Jim Work

View 1 Replies View Related

Normalize A Table With 3 Similar Columns (was Plz Help Out)

Oct 30, 2006

Hi,I am working on SQL Server.
I have a table with columns that need to be broken down. I nees to break this table:

TelNo1 TelNo2 TelNo3

555-44-33 555-43-88 555-46-89

into:


Tel_Number

555-44-33
555-43-88
555-46-89

I tried using the union statement but i just did'nt succed. Can anyone help me,please

View 1 Replies View Related

Checking Columns In Two Similar Tables

Jan 3, 2014

For every table in my database there is a duplicate table with same columns. For example, employee is the name of main table, there is employee_dup table in same database.

There is only one column extra in _dup tables i.e.,idn column.

Now, I want to know all the columns present in main table which are not present in corresponding _dup table. There might be a chance of missing one or two columns in _dup tables. So i want a query to find out all the columns present in main table that are not present in hx table.

View 6 Replies View Related

Comparing 2 Similar Columns In Different Tables

Mar 5, 2014

I am trying to write an SQL command for my crystal report. I need to compare the same column in 3different tables & get the data from each table for only the matching data.. I understand I need to create a temporary table, get the data into it & then work around.. I am quite new to SQL.

Eg: Considering one customer account
Table 1
Cust.No Name Amt_Counter AmtPaid
123.456 sam 0 0
123.456 sam 1 50

Table 2
Cust.No Name Freq_Counter Frequency
123.456 sam 1 0
123.456 sam 2 15

[code]....

View 3 Replies View Related

Fetch Similar Columns From Two Tables?

Dec 10, 2013

I am using this query to search checkbox values.

SqlCommand cmd = new SqlCommand("Select * from Details where Emp_Code in (" + selectedValues + ")", con);

I want to join a table called Materials to this now. Material table also has an Emp_Code column. How can I write a select sql query to fetch Emp_Code from both Details and Materials table.

SqlCommand cmd = new SqlCommand("select D.Emp_Code, M.Emp_Code from Details D, Materials M where D.Emp_Code = M.Emp_Code in (" + selectedValues + ")", con);

View 2 Replies View Related

Checking Columns In Two Similar Tables

Jan 3, 2014

For every table in my database there is a duplicate table with same columns. For example, employee is the name of main table, there is employee_dup table in same database.

There is only one column extra in _dup tables i.e.,idn column.

Now, I want to know all the columns present in main table which are not present in corresponding _dup table. There might be a chance of missing one or two columns in _dup tables. So i want a query to find out all the columns present in main table that are not present in hx table.

View 5 Replies View Related

Splitting A Column Into Multiple Columns?

Jun 10, 2014

I have to split a column using comma a delimiter into multiple columns. I am able to do it if i know how many column will be present in the final output. But in daily run, the columns may vary randomly.

how to split columns without hardcoding how many columns it ll come.

This is the code am using

Code:
WITH Split_Names (Fil_id,Name, xmlname)
AS
(
SELECT Fil_ID,

[Code].....

View 8 Replies View Related

Splitting 1 Column Into Multiple Columns

Apr 28, 2014

I have 1 column (memberassignment_1) that has the string in it - Port=A; Code=B; Sfx=C; Name=D; Night=E; Sig_Grp=F

I want to split this into 6 columns - Port, Code, sfx, Name, Night, Sig_Grp

EXAMPLE

PORT CODE SFX NAME NIGHT SIG_GRP
A B C D E F

View 11 Replies View Related

Splitting String Of 2 Columns In Same Table

Sep 25, 2014

I have a table name tbl_testme with columns (id,mac,keys,outputmk)

mac column have 12 character and keys have 16 character
mac keys
6545da7n9hg8 hsi457s5sd77jk87

What i want is i need to split the column into 4 characters of both column E.G.

(6545 da7n 9hg8) and (hsi4 57s5 sd77 jk87)

after splitting i need to take last 4 character of key(jk87) and last 4 character of mac(9hg8)and join them and insert that into ouputmk column.

E.G.
(jk87-9hg8-sd77-da7n-57s5-6545-hsi4)

I need this to insert in outputmk column ....

View 2 Replies View Related

Splitting Text String Into Multiple Columns

Dec 19, 2002

I'm creating a web-based NT RAS report site and am looking for the most efficient way to import the data from NT Event log into SQL2k. I'm using the 'dumpel' utility from rsc kit and all is fine except the 10th column - the message detail:

"The user DOMAINuserid connected on port Mdm15 on 08/23/2002 at 07:25am and disconnected on 08/23/2002 at 07:27am. The user was active for 2 minutes 23 seconds. 78809 bytes were sent and 50675 bytes were received. The port speed was 49300."

I need to parse this one long text string into 6 distinct columns: userID, port, duration, bytes_xmt, bytes_rcv and portspeed. After a quick review of the rowsets, the strings seem to hold a consistent output ... no real variances I can see.

I've dablled with views but am facing a small performance issue that could get bigger: The sql server not only has to run the text file import package, but also the view to format the text dump into a workable dataset, then my report code bangs over 30 queries against the final dataset. It already takes our SQL2k server over 3 minutes to parse about 20,000 rows and the server's a beast (dual 1.8 p4 cpu, 3gb ram, raid, etc).

What I think would work best is to abandon the view (performance will only get worse as the row count increases) and instead INSERT the rows into one table.

Any ideas anyone? any good scripts out there that can help me to parse the long text string quicker that using substring and replace functions?

TIA:rolleyes: :rolleyes:

View 2 Replies View Related







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