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


ADVERTISEMENT

SQL Server 2008 :: Split Values In The Column?

Oct 15, 2015

I've a table that has salescode(124!080) and salesamount(125.65!19.25) and I need to split the columns. Salesman(124) has commission(125.65). Here is the DDL:

USE tempdb;
GO
DECLARE @TEST_DATA TABLE
(
DT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
, InvNoVARCHAR(10) NOT NULL
, SalesCode NCHAR(80) NOT NULL
, Amount NCHAR(80) NOT NULL

[code]....

View 6 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 Server 2008 :: Split Single Row Into Multiple Rows Based On Column Value (quantity)

Jan 30, 2015

Deciding whether or not to use a CTE or this simple faster approach utilizing system tables, hijacking them.

SELECT s.ORDER_NUMBER, s.PRODUCT_ID, 1 AS QTY, s.VALUE/s.QTY AS VALUE
FROM @SPLITROW s
INNER JOIN master.dbo.spt_values t ON t.type='P'
AND t.number BETWEEN 1 AND s.QTY

Just wanted to know if its okay to use system tables in a production environment and if there are any pit falls of using them ?

View 1 Replies View Related

SQL Server 2008 :: Split Values In 12 Months

Oct 16, 2015

I need to split the amount equally into 12 months from Jan 2015 through Dec 2015.There is no date column in the table and the total amount has to be splitted equally.Guess I can't use Pivot here because the date column is not there ...How can I achieve this ?

CREATE TABLE #tbl_data (
Region Varchar(25),
Amount FLOAT,

[code]...

View 4 Replies View Related

SQL Server 2008 :: Split Varchar Variable To Multiple Rows And Columns Based On Two Delimiter

Aug 5, 2015

declare @var varchar(8000)
set @var='Name1~50~20~50@Name2~25.5~50~63@Name3~30~80~43@Name4~60~80~23'

---------------------

Create table #tmp(id int identity(1,1),Name varchar(20),Value1 float,Value2 float,Value3 float)
Insert into #tmp (Name,Value1,Value2,Value3)
Values ('Name1',50,20,50 ), ('Name2',25.5,50,63 ), ('Name3',30,80,43 ), ('Name4',60,80,23)

select * from #tmp

I want to convert to @var to same like #tmp table ..

"@" - delimiter goes to rows
"~" - delimiter goes to columns

View 6 Replies View Related

SQL Server 2008 :: IPAddress - How To Split One Column Into Two Columns

Mar 6, 2015

I have a ipaddress column is there where i need to split the column into two columns because of

values like below

172.26.248.8,Fe80::7033:acba:a4bd:f874
172.26.248.8,Fe80::7033:acba:a4bd:f874

172.26.248.8,Fe80::7033:acba:a4bd:f874

I have written the below query but it will throw some error.

select SUBSTRING(IPAddress0, 1, CHARINDEX(',', IPAddress0) - 1) as IPAddress0
from IPADDRESS

error:

Msg 537, Level 16, State 2, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.

View 1 Replies View Related

SQL Server 2012 :: Normalize Values From Column Split Using Tally Table?

Apr 23, 2014

I have a table that has the following structure:

EntryID int,
Categories varchar(200)

values look like:

541,'A,B,C'
345,'B,C'
234,'A,C'
657,'D,E'
435,'D'

what I want to do is extract the Categories column to a normalized separate table:

541,'A'
541,'B'
541,'C'
345,'B' ....

I found the split using the tally table useful to split one-by-one, but how can it be applied when you are referring to a table?

View 2 Replies View Related

SQL Server 2008 :: How To Find Time Difference Between Two Rows Of Record

Nov 6, 2015

I have the table with the similar set of records which mentioned below, find the time difference between two rows of record. By Using the MsgOut column i have to find time taken b/w PS & PV and some record doesnt have PV .

LogID LocIDClientCert MsgType MsgOutMessageTimeStamp System
1151334934NOT SPECIFIEDQ_T12PS 2015-10-01 00:00:40.980AHR
1151335243NOT SPECIFIEDD_T12PV 2015-10-01 00:00:53.800AHR
1151342944NOT SPECIFIEDQ_T12PS 2015-10-01 00:05:40.957AHR
1151343281NOT SPECIFIEDD_T12PV 2015-10-01 00:05:53.670AHR
1151350046NOT SPECIFIEDQ_T12PS 2015-10-01 00:10:40.970AHR
1152760563759NOT SPECIFIEDQ_T12PS 2015-10-01 15:28:29.617AHR
1152760739690NOT SPECIFIEDQ_T12PS 2015-10-01 15:28:33.633AHR

View 6 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

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

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

SQL Server 2008 :: Concatenate Column Name With Values

Sep 17, 2015

I want to concatenate the column_names with the respective values.

Create table #test1 (Id int,Name varchar(10),Country varchar(10))

insert into #test1 values ( 1,'JOHN','USA'),
(2,'SAM','CANADA'),
(3,'HO','CHINA'),
(4,'RAM','INDIA')

select * from #test1

I have temp table with columns (Id,Name, Country). I want to concatenate column_name with their respective values; means i want column_header with every value for the column and then i want to concatenate multiple columns. I am looking for something like below:

ID values
1,NAME-john-COUNTRY-USA
2,NAME-SAM-COUNTRY-CANADA
3,NAME-HO-COUNTRY-CHINA
4,NAME-RAM-COUNTRY-INDIA

Note: This is just a sample .i am looking something dynamic because i have around 50 tables.i can do with query below but since i don't have static columns and table i am looking for something dynamic

select ID, (a+'-'+B) as Value from (
select ID,'NAME'+'-'+NAME as a,'Country'+'-'+Country as b from #test1

View 2 Replies View Related

SQL Server 2008 :: Time Difference With Dates In Same Column

Mar 17, 2015

How do I find the time difference when the dates are in one column? I need to find hours and minutes between each row.

CREATE TABLE #Time ([TimeStamp] DATETIME, TimeDiff INT)
INSERT INTO #Time (TimeStamp)
VALUES ('2014-09-02 07:51:02.810'), ('2014-09-02 07:48:09.567'), ('2014-09-02 08:37:09.647')
, ('2014-09-02 16:16:42.593'), ('2014-09-02 08:06:13.387'),('2014-09-02 14:32:00.113')
DROP TABLE #Time

View 6 Replies View Related

Split Values From Within Column

May 7, 2008


I have been trying to separate firstname,last name,middle from name column

Existing Format
Column Name =FIRST,LAST M

Desired
First
Last
M

I would llike to divide one column into three columns...How can i achieve it..

Please let me know

View 5 Replies View Related

SQL Server 2008 :: Get Time Difference Of Char Datatype Column Value

May 29, 2015

How can I get time difference of the following record :

STARTTIME ENDTIME
3:30 PM 4:30PM
7:30 PM 8:30PM

I have tried it by below query,

SELECT CONVERT(TIME,STARTTIME,108) - CONVERT(TIME,ENDTIME,108) FROM BATCH_MASTER

but it gives following error message

[color=red]Operand data type time is invalid for subtract operator.[/color]

View 6 Replies View Related

Transact SQL :: Split Values In Column

Oct 15, 2015

I've a table that has salescode(124!080) and salesamount(125.65!19.25) and I need to split the columns. Salesman(124) has commission(125.65). Here is the DDL:

USE tempdb;
GO
DECLARE @TEST_DATA TABLE
(
DT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
, InvNoVARCHAR(10) NOT NULL
, SalesCode NCHAR(80) NOT NULL

[code]....

View 11 Replies View Related

SQL Server 2008 :: How To Fetch Drop Down Values Stored In XML Column

Aug 13, 2015

Below is a XML column data. How to get the Id and respective Names for "Case Manager" Dropdown ONLY in SQL server 2008. I don't want to get anything related to "Intake Staff" drop down.

<DropDown Prompt="Case Manager" Column="case_manager" AddOnly="false" ReadOnly="false" Required="false" CanHaveNotes="no" LabelCssClass="" IndentLevel="1" FirstEntryBlank="false" CssClass="" DefaultValue="" ChoiceType="1">
<Items>
<Item Id="1">ABC</Item>
<Item Id="2">DEF</Item>
<Item Id="3">GHI</Item>
<Item Id="4">JKL</Item>
<Item Id="5">MNO</Item>

[code]....

View 1 Replies View Related

SQL Server 2008 :: Return All Rows From Any Table Containing Value Range In Specified Column?

Sep 3, 2015

I have several databases to deal with, all with + 250 tables. The databases are not identical and do not conform to a specific naming convention for table names. Most but not all tables have a column called "LastUpdated" containing a date/time (obviously). I'd like to be able to find all rows within a whole database (table by table) where the date/time is greater than a specified date/time.

I'm looking for a reliable query that will return all the rows in each of the tables but without me having to write hundreds of individual scripts "SELECT * FROM [dbo.xyz] WHERE LastUpdated > '2015-01-01 09:00:00:000'", or have to look through each table first to determine which of them has the LastUpdated field.

View 9 Replies View Related

SQL Server 2008 :: Calculated Time Difference Column In Create Table

Jul 14, 2015

How to include a time difference column using 2 other date columns during creation of a table ?

The requirement is to create a table and include the following columns:

1.Downtime start date & time
2.Downtime end date & time
3.Downtime Duration in hh:mm (calculated date difference between column 1 and 2)

View 3 Replies View Related

T-SQL (SS2K8) :: Split The Column And Parse Values

Aug 18, 2014

I have a column with the following information in it. I need to split the column and parse the values. I have Parsed one single values from but, not multiple times.

<TV<MR1#4.0#true#2.0#USD>VT>,<TV<MR2#3.0#true#1.5#USD>VT>,<TV<MR3#0.0#true#0.0#USD>VT>,<TV<MR4#0.375#true#0.19#USD>VT>

MR1 -Model Code,
4.0 - Percentage,
true - isAbs,
2.0 - AppliedValue --> This is Actual amount applied as tax.
USD - Currency

For the example provide , the totalTaxValue would be 2.0 + 1.5+0.0+0.19 = 3.69

View 5 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

SQL Server 2008 :: Select Multiple Values From Same Column And Make Them To Show In A Row

Jun 10, 2015

I created a query that got the following result. But I expect to get the structure like, care_nbr, cust_nbr,legal_name, address_type=physical address, addr_line_1, addr_line_2, address_type-primary address, ddr_line_1, addr_line_2. That means I only need primary and physical address, and expect them to show in a row to each care_nbr. How to perform that?

CARE_Nbr||Cust_Nbr||Legal_Name||||||| Address_Tpye |||Addr_Line_1 ||||||||||||||||Addr_Line_2
99000001||004554||Mac Marketing, LLC||Billing Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Mailing Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Primary Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Physical Address||210 Parktowne Blvd Suite 1||NULL

How should I modify this query to get my expected result?

select a.CARE_Number,
a.Customer_Nbr_Txt,
a.Customer_Type_Txt,
a.Legal_Name_Txt,
c.Address_Type_Txt,c.Address_Line_1_Txt,c.Address_Line_2_Txt,

[code]....

View 6 Replies View Related

SQL Server 2008 :: Rows Holding Group-wise Maximum Of Certain Column?

May 13, 2015

is there any more efficient way for example to implement the next query?

SELECT s1.article, dealer, s1.price
FROM shop s1
JOIN (
SELECT article, MAX(price) AS price
FROM shop
GROUP BY article) AS s2
ON s1.article = s2.article AND s1.price = s2.price;
WHERE dealer = 'dealer sample'

What indexes I should create for this query?

View 2 Replies View Related

Transact SQL :: Split A Column Which Contains Ntext Values Separated With Value

Jul 13, 2015

SQL syntax to split a column that contains ntext values that are separated with this value : ;#

I need it to create 3 new columns

i.e.: I have a column that contains values such as:

ROW1: ;#typea;#typeb;#typec;#typed;#
ROW2: ;#typeb;#typec;#
ROW3: ;#typeb;#

I need it to convert to 3 new columns and strip out the ;#:

E.g.:

Column 1
typea
typeb
typeb

Column 2
typeb
typec
blank

View 16 Replies View Related

SQL Server 2012 :: Merge Column Values Into Rows?

Aug 12, 2015

I need to merge column values (#Status.Status) based on OrderID onto #Orders.NewStausCombined field separated by commas .

CREATE TABLE #Status
(
ID INT IDENTITY (1,1) PRIMARY KEY,
OrderID INT,
Status VARCHAR(20)
)
INSERT INTO #Status ( OrderID, Status )

[code].....

View 3 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 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 :: Selecting Rows As Column Name And Other Column Values As Rows

Jun 25, 2015

I have questions and answers from one table, I need to select questions as column names and answers column values as the results for the questions column.

View 28 Replies View Related

SQL Server 2008 :: No Option To Automatically Partition Table Based On Unique Values Of Column?

Jun 17, 2015

A common partitioning scenario is when the partition column has the same value for every record in the partition, as opposed to a range of values. Am I the only person who wonders why there isn't an option to automatically partition a table based on the unique values of the partition column? Instead of defining a partition function with constants, you ought to be able to just give it the column and be done. This would be particularly valuable for tables partitioned on a weekly or monthly date; when new data is added it could simply create a new partition if one doesn't already exist.

View 4 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

SQL Server 2008 :: SSIS Derived Column Transformation Failing On Converting Blank Rows

Jul 30, 2015

I have flat file source from which data is imported to a Sql table.The target column is int and input column is string .The column has some numeric values and some blank values.when I tried to convert into int values it fails.

View 7 Replies View Related

SQL Server 2008 :: How To Pivot Unknown Number Of Rows To Columns Using Data As Column Headers

Sep 10, 2015

I have a single table that consist of 4 columns. Entity, ParamName, ParamsValue and ParamiValue. This table stores normalized Late Fee related parameters for apartments. The Entity field contains a code that identifies the apartment complex. The ParamName in a textual field that contains the name of the parameter that the other 2 fields define the value for; ParamsValue and ParamiValue. If the Late Fee parameter (as named in ParamName is something numerical then the value for that parameter can be found in ParamiValue else its in ParamsValue.

I don't know if 'Pivot' is the correct term to use for describing what I am trying to do because I've looked at the Pivot examples and I don't see how that will work for this. Using the Table and data as provided below, how would I construct a query so that I get 1 row per Entity in which the columns are the ParamsValue or ParamiValue for the ParamName listed in the column header (for the query)?

Below is the DDL to create the table and populate it.

USE [DBA_UTIL]
CREATE TABLE [dbo].[PARAMEXAMPLE](
[Entity] [varchar](16) NULL,

[Code]....

View 4 Replies View Related







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