How To Split Out Table Rows Into 3 Tables

Mar 14, 2006

I imported all rows of my txt file using SSIS 2005 into a table.  I am now trying to figure out how to split out the header, payment rows, and maintenance rows.  First, some information.

An example of table results is here:
http://www.webfound.net/split.txt
The table has just one field of type varcha(100) because the incoming file is a fixed length file at 100 bytes per row

The header rows are the rows with HD in them...then followed by detail rows for that header (see here http://www.webfound.net/rows.jpg).

I need to

1) Split out the header into a header table
2) Split out the maintenance rows (related to the header) into a maint table
3) Split out the payment rows (related to the header) into a payment table

I'll need to maintain a PK/FK relationship between each Header and it's corresponding maint and payment rows in the other 2 tables.

To determine if it's a payment vs. maintenance row, I need to compare chars 30 - 31.  If it contains 'MT' then you know it's a maintenance row, else it's a payment row.

How in the hell do I do this???

View 4 Replies


ADVERTISEMENT

How To Assign Unique PKs And FKs On Split Of Txt Rows Into DB Tables

Mar 14, 2006

SSIS 2005

Ok, I have a task in SSIS that does the following and works:

1) Brings in a txt file

2) Using a conditional component, checks for a value in the row.

3) Based on the value, splits the row into one of 3 tables (Header, Maintenance, or Payment)

Here is a print screen of what I have so far which splits Header rows into it's own table, Maintenance rows into its own table, and Payment Rows into its own table:

http://www.webfound.net/qst_how_to_add_header_PK_and_FKs.JPG

Here is a print screen of the conditional split:

http://www.webfound.net/conditional_split.jpg

Please take a look at the txt file here before it's processed:

http://www.webfound.net/split.txt

http://www.webfound.net/rows.jpg

Notice that the pattern is a header row, followed by it's corresponding detail rows.  The detail rows are either Maintenance or Payment rows. 

I need to somehow during the Script component or some other way, to assign a unique HeaderID (PK) to each of the header rows and add that ID to it's corresponding Maintenance and Payment detail rows in their corresponding tables as a PK.  The problem is

1) I don't know how to do this in the flow of the components as I have it now

2) How do I tell it to create a new Header ID and Header FKs for the detail rows based off of each new Header row?

In the end (much later on in my entire package), the goal is to be able to run a stored proc to join and select the Header and Details rows back into a final table so I can then do more processing such as split each header and detail rows into their own txt files, etc....I don't need to go into details why but just know that this is the goal, therefore I need to relate each header row with their corresponding detail rows that are split off into a MaintenanceRow and PaymentRowTable

 

View 2 Replies View Related

SQL Server 2008 :: Joining Two Tables - Split Rows Into Column

Sep 29, 2015

I am trying to join two tables and looks like the data is messed up. I want to split the rows into columns as there is more than one value in the row. But somehow I don't see a pattern in here to split the rows.

This how the data is

Create Table #Sample (Numbers Varchar(MAX))
Insert INTO #Sample Values('1000')
Insert INTO #Sample Values ('1024 AND 1025')
Insert INTO #Sample Values ('109 ,110,111')
Insert INTO #Sample Values ('Old # 1033 replaced with new Invoice # 1544')
Insert INTO #Sample Values ('1355 Cancelled and Invoice 1922 added')
Select * from #Sample

This is what is expected...

Create Table #Result (Numbers Varchar(MAX))
Insert INTO #Result Values('1000')
Insert INTO #Result Values ('1024')
Insert INTO #Result Values ('1025')
Insert INTO #Result Values ('109')
Insert INTO #Result Values ('110')

[Code] ....

How I can implement this ? I believe if there are any numbers I need to split into two columns .

View 2 Replies View Related

SQL View To Split Rows In Single Table...

May 24, 2007

I've been searching the forums and Google for some help splitting up rows in a table without any luck. I'm not quite sure what to even look for

I have a table is MSSQL 2000 that looks as follows:


Code:



id custnum b1_email b2_email b1_sub b2_sub
------------------------------------------------------------------------
1 123456 b1@host1.com b2@host1.com 0 0
2 654321 b1@host2.com b2@host2.com 1 0
3 321654 b1@host3.com b2@host3.com 0 1



Now... I am hoping create a view that splits these rows up so that only a single email address is on each row. I'd like to split it up as follows:


Code:



custnum email sub
----------------------------------
123456 b1@host1.com 0
123456 b2@host1.com 0
654321 b1@host2.com 1
654321 b2@host2.com 0
321654 b1@host3.com 0
321654 b2@host3.com 1



Any help would be great! I imagine some sort of join command can be constructed using a single table?

View 2 Replies View Related

Split String And Insert Into Table Rows

Apr 17, 2014

I have a string which is delimited by commas:

Green,Red,Blue,Orange,Black,White

I need to create a SQL statement to read the string, split it at the "," character, and insert it into individual rows. I also need to insert an ID (the same for all split values).

For instance:

INSERT INTO tbl_Temp (unique_id, ID, color) VALUES (1, 999, Green)
INSERT INTO tbl_Temp (unique_id, ID, color) VALUES (2, 999, Red)
INSERT INTO tbl_Temp (unique_id, ID, color) VALUES (3, 999, Blue)
etc...

I would like to be able to do this all in one statement so I don't have to break it out into individual SQL statements.

View 4 Replies View Related

Big Table(?) Or Split Between Tables?

Nov 20, 2007

Hi Guys

I have an application that runs on several sites that has a table with 36 columns mostly ints och small varchars.

I currently have only one table that stores the data and five indexes and since the table on one location (and others soon) has about 18 million rows I have been trying to come up with a better solution (but only if needed, I dont think I have to tell you that I am a programmer and not an dba).
The db file size with all the indexes is more then 10gb, in it self is not an problem but is it a bad solution to have it that way?

The questions are:

Are there any big benefits if i split it into several smaller tables or even smaler databases and make the SPs that gets the data aware that say 2006 years data is in table a and so on?
Its quite important that there are fast SELECTS and that need is far more important then to decrease the size of the database file and so on.

How many rows is okay to have in one table (with 25 columns) before its too big?

Thanks in advance.

Best regards
Johan, Sweden.

CREATE TABLE [dbo].[Cdr](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Abandon] [varchar](7) NULL,
[Bcap] [varchar](2) NULL,
[BlId] [varchar](16) NULL,
[CallChg] [varchar](6) NULL,
[CallIdentifier] [uniqueidentifier] NULL,
[ChgInfo] [varchar](5) NULL,
[ClId] [varchar](16) NULL,
[CustNo] [smallint] NULL,
[Digits] [varchar](32) NULL,
[DigitType] [varchar](1) NULL,
[Dnis1] [varchar](6) NULL,
[Dnis2] [varchar](6) NULL,
[Duration] [int] NULL,
[FgDani] [varchar](13) NULL,
[HoundredHourDuration] [varchar](3) NULL,
[Name] [varchar](40) NULL,
[NameId] [int] NOT NULL,
[Npi] [varchar](2) NULL,
[OrigAuxId] [varchar](11) NULL,
[OrigId] [varchar](7) NULL,
[OrigMin] [varchar](16) NULL,
[Origten0] [varchar](3) NULL,
[RecNo] [int] NULL,
[RecType] [varchar](1) NOT NULL,
[Redir] [varchar](1) NULL,
[TerId] [varchar](7) NOT NULL,
[TermAuxId] [varchar](11) NULL,
[TermMin] [varchar](16) NULL,
[Termten0] [varchar](3) NULL,
[Timestamp] [datetime] NOT NULL,
[Ton] [varchar](1) NULL,
[Tta] [int] NULL,
[Twt] [int] NULL,
[DateValue] [int] NULL,
[TimeValue] [int] NULL,
[Level] [varchar](50) NOT NULL CONSTRAINT [DF_Cdr_Level] DEFAULT ('x:'),
CONSTRAINT [PK_Cdr] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 10) ON [PRIMARY]
) ON [PRIMARY]

View 14 Replies View Related

T-SQL To Split Data From One Table Into Two Tables?

Feb 21, 2005

What's the best way to convert a large set of records from a simple schema where all fields are in one table to a schema where fields are split across two tables? The two table setup is necessary for reasons not worth getting into here.

Doing this via cursor is pretty straightforward, but is there a comparable set-based solution?

Here are sample create table commands. Obviously, the example below is simplified for discussion purposes.


-- One record from here will produce a record in TargetParentRecords and a record in TargetChildRecords for a total of two records.
CREATE TABLE OriginalSingleTableRecords (
ID INT IDENTITY (1, 1) NOT NULL,

ColumnA VARCHAR(100) NOT NULL,
ColumnB VARCHAR(100) NOT NULL,

CONSTRAINT PK_OriginalSingleTableRecords PRIMARY KEY CLUSTERED (ID)
)

CREATE TABLE TargetParentRecords (
ParentID INT IDENTITY (1, 1) NOT NULL,

ColumnA VARCHAR(100) NOT NULL,

CONSTRAINT PK_TargetParentRecords PRIMARY KEY CLUSTERED (ParentID)
)

-- Each row in this table must link to a TargetParentRecords row
CREATE TABLE TargetChildRecords (
ID INT IDENTITY (1, 1) NOT NULL,

ParentID INT NOT NULL, -- References TargetParentRecords.ParentID
ColumnB VARCHAR(100) NOT NULL,

CONSTRAINT PK_TargetChildRecords PRIMARY KEY CLUSTERED (ID)
)

View 5 Replies View Related

Need To Find An Easy Way To Split A Column In Table Without Using Cursor Or Temp Tables

Sep 5, 2007

Hi ,
I have two tables within a SQL database. The 1st table has an identified column and column which lists one of more email identifers for a second table,
e.g.
ID Email
-- ----------
1 AS1 AS11
2 AS2 AS3 AS4 AS5
3 AS6 AS7

The second table has a column which has an email identifier and another column which lists one email address for that particular identifier, e.g.
ID EmailAddress
--- ------------------
AS1 abcstu@emc.com
AS2 abcstu2@emc.com
AS3 abcstu3@emc.com
AS4 abcstu4@em.com
AS5 abcstu5@emc.com
AS6 abcstu6@emc.com
AS7 abcstu7@emc.com
AS11 abcstu8@emc.com
I need to create a stored procedure or function that:
1. Selects an Email from the first table, based on a valid ID,
2. Splits the Email field of the first table (using the space separator) so that there is an array of Emails and then,
3. Selects the relevant EmailAddress value from the second table, based on a valid Email stored in the array
Is there any way that this can be done directly within SQL Server using a stored procedure/function without having to use cursors?

Many Thanks,
probetatester@yahoo.com

View 7 Replies View Related

Transferring Rows From One Table To 2 Tables

Oct 19, 2007

Hi,

I have a table in Sql 2005 called
Customers
CustomerId
CustomerName
CustomerAge
CustomerRank
CustomerStCode

I have to transfer the records into 2 tables

CustomerMaster
CustomerId
CustomerStCode

CustomerDetails
CustomerId
CustomerName
CustomerAge
CustomerRank

I have to pick up a row from Customers and transfer it to CustomerMaster and CustomerDetails. CustomerId of CustomerMaster will be the CustomerId of CustomerDetails while transfer.

How to do this?

thanks

View 11 Replies View Related

Copying Rows From Multiple Tables To A Single Table

Sep 20, 2007



Hi,

I have 3 tables with the follwing schema
Table <Category>
{

UniqueID,
LastDate DateTime
}


Assume the follwing tables with data following the above schema

Table Cat1
{

1, D1
2, D2
3, D3
}
Table Cat2
{

2, D4
3,D5
4, D6
}
Table Cat3
{

1, D7
3,D8
5,D9
}

I have a Master and the schema is as follows
Table master
{

UniqueId,
Cat1 DateTime, -- This is same as the Table name
Cat2 DateTime, -- This is same as the Table name
Cat3 DateTime -- This is same as the Table name
}

After inserting the data from all these 3 tables, I want the my master table to look like this
Table Master
{

UniqueId cat1 cat2 Cat3
------------ --------- ------- -----------
1 D1 NULL D7
2 D2 D4 NULL
3 D3 D5 D8
4 NULL D6 NULL
5 NULL NULL D9
}


Please remember the column names will be same as that of table names

can any one pelase let me know the query t o acheive this

Thanks for your quick response
~Mohan Babu

View 8 Replies View Related

Split Rows

Aug 21, 2007

not sure if this is possible...

but lets say i make a select like

select products, stock from table

and my rs is

chair | 1
couch | 3
lamp | 2

is there anyway in the select to make any row that has stock of more than 1 to make a new row... so my rs would come back as

chair
couch
couch
couch
lamp
lamp

Any info would be helpful...

Thanks,

~ moe

View 4 Replies View Related

Insert Multiple Rows To Table Based On Values From Other 2 Tables.

Nov 21, 2007

I have a form to assign JOB SITES to previously created PROJECT.  The  JOB SITES appear in the DataList as it varies based on customer. It can be 3 to 50 JOB SITES per PROJECT.
I have "PROJECT" table with all necessary fields for project information and "JOBSITES" table for job sites. I also created a new table called "PROJECTSITES"  which has only 2 columns:  "ProjectId" and "SiteId".
What I am trying to do is to insert multiple rows into that "PROJECTSITES" table based on which checkbox was checked.  The checkbox is located next to each site and I want to be able to select only the ones I need. Btw the Datalist is located inside of a formview and has it's own datasource which already distincts which JOBSITES to display.
Sample:
ProjectId    -    SiteId
1   -   5
1    -   9
1    -   16
1    -   18
1    -   20
1    -   27
1    -   31
ProjectId stays the same, only values for SiteId are being different.
I hope I explaining it right. Do I have to use some sort of loop to go through the automatically populated DataList records and how do I make a multiple inserts to database table? We use SQL Server 2005 and VB for code behind. Please ask if I missed on some information. Thank you in advance.

View 10 Replies View Related

SQL 2012 :: Split Row Into Three Or More Rows?

Feb 26, 2014

I am trying to split a row that currently has information like this:

area: north,south,west

however I want the area column to display the information like this:

Area:
North
South
West

I have wrote a split function and it worked fine in other work I did...

View 5 Replies View Related

Can I Split Matrix Into 2 Or 3 Rows?

Nov 28, 2007

Hi All

I have a matrix with single row. The no. of column varies and sometimes goes to 10-15. So it goes to next page and while exporting it inserts blank pages when exported to PDF. I need the column width at least 2.5cm. I need to break the matrix to next row instead of it going to next page say after 6th or 8th column. I tried to work with the example given in the site http://blogs.msdn.com/chrishays/archive/2004/07/23/HorizontalTables.aspx by Chris Hays. But it is showing matrix for each Row Group, which doesn't meet my requirement.


I had a work around which worked by putting two matrix one below the other and filtering the columns to be shown in each matrix.

If anybody faced this issue or anybody solved the issue kindy reply which will be very helpful for me.


One more doubt, Can I get the Column number of the matrix?

Thanks in advance

Dileep

View 3 Replies View Related

How To Split Data Into Two Rows

Jun 22, 2007

I have a query that returns a table similar to:

State Status Count
CA Complete 10
CA Incomplete 200
NC Complete 20
NC Incomplete 205
SC Incomplete 50


What sort of query will allow me to reformat the table into:

State Complete Incomplete
CA 10 200
NC 20 205
SC NULL 50

View 5 Replies View Related

Split Single Row To Multiple Rows

Feb 27, 2012

I have a table which looks like

Low High
-------------------------
cx01 cx04
sn05 sn08

I need output like

Result
-------------
cx01
cx02
cx03
cx04
sn05
sn06
sn07
sn08

How to get this output using sql query?

View 8 Replies View Related

How To Split Columns Into Multiple Rows

Jun 7, 2008

SOURCE TABLE
ID DESCRIPTION
1 I am a programmer
2 I am a doctor

Destination Table

ID LINE DESCRIPTION(Varchar10)
1 1 I am a pro
1 2 grammer
2 1 i am a doc
2 2 tor


Please someone help me on this.

View 4 Replies View Related

Split String And Insert Into Rows

Apr 4, 2014

I have a string stored in field v_images in table tbl_DealerFeed_temp. This string is split by the "|" character. I want to create a SQL query to grab this string, split it, and insert it into tbl_Vehicles_photos. I also need to pass the value d_id into the photos table:

tbl_Vehicles_photos:
vp_id, d_id, vp_image_name

The vp_id is the unique ID. I need to populate the d_id and vp_image_name field with my values.

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

How Can I Split The Rows In 2 Textboxes - Urgent

Feb 20, 2007

Hi,

I have a report and its been populating from a sproc. and i have 2 text boxes called both of them are poplulated by Fields!Investment Names, but right i can display the data left to right but i want to display the Data starting top to bottom and then towards the right.

I tried grouping the data in this way for one text box = CountRows()/2 > 10 . and this shows all the records one below the other, so is there a way that i can display half the records in one text box and the other half in the other text box.

I am going kinda nuts over this. Can someone please help me.

Regards

Karen

View 15 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 A Row Into Multiple Rows Based On Column

Sep 5, 2014

I need to split a row into multiple rows based on multiple column values.

time_id = 111
employee_id = 222
time_in = 10:00
time_out = 16:00
break1_in = 12:00
break1_out = 13:00
break2_in = 14:00
break2_out = 15:00

I would like to break this into multiple time_in/time_out based on if they have breaks. Breaks are not required and will come across blank if non are taken.

row 1
time_in 10:00
time_out 12:00

row 2
time_in 13:00
time_out 14:00

row 3
time_in 15:00
time_out 16:00

View 2 Replies View Related

Split One Row Into Multiple Rows Based On Time Elements

Feb 5, 2007

I'm dealing with a problem.

The record information example

DateTimeStart , DateTimeEnd , action , duration (seconds)
2007-02-02 10:30:22 , 2007-02-02 11:30:22 action1 , 600

what i want is for every half hour between start and end a record

10.30 action1
11.00 action1
11.30 action1

how can i create this, i'm a little stuck on this

View 2 Replies View Related

SQL 2012 :: Split Column Data Into Multiple Rows

Apr 1, 2015

How to split a column data into multiple rows, below is the requirement...

Create table #t3 (id int, country (varchar(max))

INSERT #t3 SELECT 1,' AU-Australia
MM-Myanmar
NZ-New Zealand
PG-Papua New Guinea
PH-Philippines'

Output should be like below

1 ,AU-Australia
1,MM-Myanmar
1,NZ-New Zealand
1,PG-Paua New Guinea
1,PH-Phlippines

Note: we are getting source data from sqlserver tables.

I googled and found below way but did't get the output as required

SELECT A.id, a.country,
Split.a.value('.', 'VARCHAR(500)') AS String
FROM (SELECT id, country ,
CAST ('<M>' + REPLACE(country, ' ', '</M><M>') + '</M>' AS XML) AS String
FROM #t3) AS A CROSS APPLY String.nodes ('/M') AS Split(a);

View 4 Replies View Related

How To Split Comma Separated Values And Show As Rows

Apr 19, 2013

I have an requirement where i need to show Employee Table and CustomerMeta Table joins In CustomerMeta Table (CustID)

Reference to Employee Table and Metavalues table Metavalues table is like master table.

In Application i will get multiple select box selection (DrivingLicense,Passport etc;) so that data will be inserted in comma(',') separated values So in my desired output i need to show as i need to show split those comma separated and for every MetaTypeID MetaTypeName as a row as showed in desired output

Metavalues table :

MetaID Metavaluedescription
1 Driving License
2 Passport
3 AadharCard
4 EducationalProof
5 ResidentialProof

CustomerMeta Table :

CustID MetaTypeID MetaTypeName
2 1,2,3,4,5 DrivingLicense,Passport,AadharCard,EducationalProof,ResidentialProof
3 1,2,3DrivingLicense,Passport,AadharCard

Employee Table

EmpID CustID EmPname
1001 2Mohan
1002 3 ramu

Desired OutPut :

EMPID CustID EmPname MetaTypeID MetaTypeName
1001 2 Mohan 1 Driving License
1001 2 Mohan 2 Passport
1001 2 Mohan 3 AadharCard
1001 2 Mohan 4 EducationalProof
1001 2 Mohan 5 ResidentialProof
1002 3 ramu 1 Driving License
1002 3 ramu 2 Passport
1002 3 ramu 3 AadharCard

View 20 Replies View Related

How To Split A Delimited Column Into Mulitple Rows In The Dataflow?

Jan 19, 2006

I'm sure there is probably a very easy solution that I am just not seeing or can't Google...

I have a DataFlow that includes a column of Delimited values (i.e. Value1,Value2,etc..). As this DataFlow is populating a parent table, I need split the values into their own dataflow and populate a child table. I've tried a script transformation and couldn't figure out how to accept 1 delimited input row and output multiple rows after a split. Any ideas?

TIA,
Matthew

View 1 Replies View Related

Transact SQL :: Split Rows By Day / By Datetime And Partition By Columns

Jul 22, 2015

I am trying to spilt records into days by the start - End datetime.

I would send an image and data but because I am new to the forum, I am blocked sending images.

"Body text cannot contain images or links until we are able to verify your account"

How I can forward an image.

View 15 Replies View Related

Split Tables And Keep ID

Sep 27, 2005

I have a large table that I'm planning on splitting out into 5 smaller ones. What I need to do is maintain some central repository for auto-numbering new records to make sure that no 2 records in different tables have the same unique ID. Thanks in advance!

View 14 Replies View Related

SQL Server 2008 :: How To Split Time Column Values Into Rows

Jun 6, 2015

I have the table as

|start || end1 |

1/06/2015 1:00 || 1/06/2015 1:30
1/06/2015 2:00 || 1/06/2015 3:00
1/06/2015 3:20 || 1/06/2015 4:00
1/06/2015 4:00 || NULL

I want the output as : -

|start || end1 |

1/06/2015 1:00 || 1/06/2015 1:30
1/06/2015 1:30 || 1/06/2015 2:00
1/06/2015 2:00 || 1/06/2015 3:00
1/06/2015 3:00 || 1/06/2015 3:20
1/06/2015 3:20 || 1/06/2015 4:00
1/06/2015 4:00 || NULL

I am trying the below mentioned code but it is not giving me the desired output..

with cte as
(select
start
,end1
,ROW_NUMBER() over (order by (select 1)) as rn

[Code] .....

I am getting wrong output as -

| start || end1 |

1/06/2015 1:00 || 1/06/2015 1:30
1/06/2015 1:30 || 1/06/2015 2:00
1/06/2015 2:00 || 1/06/2015 4:00
1/06/2015 4:00 || 1/06/2015 4:00

View 1 Replies View Related

SQL Server 2014 :: Query To Split String As Rows And Columns

Oct 19, 2015

I have a string that contains series of parameters with separators.i need to split the parameters and its values as rows and columns.e.g string = "Param1 =3;param2=4,param4=testval;param6=11;..etc" here the paramerter can be anything and in any number not fixed parameters.
Currently am using the below function and getting the parameters by each in select statement as mentioned below.

select [dbo].[rvlf_fn_GetParamValueWithIndex]('Param1=3;param2=4,param4=testval;param6=11;','param1=',';') as param1,
[dbo].[rvlf_fn_GetParamValueWithIndex]('Param1=3;param2=4,param4=testval;param6=11;','param2=',';') as param2
CREATE FUNCTION [dbo].[rvlf_fn_GetParamValueWithIndex]
(
@CustomProp varchar(max),

[code]....

View 8 Replies View Related

Transact SQL :: Split Column To Rows Which Is Delimited Using Commas And Semicolons

Jul 9, 2015

All I have a situation where I need to split the column to rows which is delimited using commas and semicolons. Please find the below sample data.

Data in Tables

Test1, Test2, Test3
Test4;Test5;Test6

Desired output
Test1
Test2
Test3
Test4
Test5
Test6

Is there any way that I can get this output in SQL other than using the XML Conversion, since the data has some special characters in this.

View 7 Replies View Related

Transact SQL :: How To Split Comma Separated Columns Into Separate Rows

Sep 14, 2015

I have values in two columns separated by commas, like shown below:

I need the Output Like

How to do this in SQL server ?

View 6 Replies View Related

Transact SQL :: Split Rows Based On Time Difference More Than 1day

Aug 5, 2015

equipmentid downtimestartdate downtimeenddate  dowtime
a3er 2015-03-15 02:00 2015-03-17 23:00            69
b6e4 2015-03-18 13:00 2015-03-20 04:00            39

i have many rows(in our production table, thousands of rows are there) like above in a table and i want like below output(in output total 6rows only)

equipmentid downtimestartdate downtimeenddate dowtime
a3er          2015-03-15 02:00 2015-03-15 24:00            22 
a3er          2015-03-16 00:00 2015-03-15 24:00            24
a3er          2015-03-17 00:00 2015-03-15 23:00            23

[code]...

View 2 Replies View Related







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