Splitting Single Row Of Table In To Two Rows

May 24, 2007

Hi All,

I was wondering is there any way by which i can split a single row in my table into two rows in my table in design of my report. In my table there are 16 rows i cant design table with 16 rows as it will increase size of my report body & it will give problem while exporting. Instead what i want to do is , design a table with two header rows first header with first 8 columns & second with remaining 8 columns.

The table should look like this

//column 1 2

ID NAME

AGE SEX

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

2 abc

10 M

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

3 def

20 M

Is it possible

Thanks

-- Darshan

View 4 Replies

Splitting Up Of Values In Single Column

May 21, 2008

Hi,

I have a table that has multiple postal codes in one of the columns. Those have to be split up one per line and stored in another table. The zip codes are comma seperated. Is there a function that can do this...?

Example data in ZipCodeTable. (Name and ZipCode are 2 columns in a table)

NameZipCode
Area119930,19970,19971,19944
Area219934,19938,19901,19903,19904
Area319994,19838

output Table should be:

NameZipCode
Area119930
Area119970
Area119971
Area119944
Area219934
Area219938
Area219901
Area219903
Area219904
Area319994
Area319838

Any thoughts on this would be of much help !!..

Thanks

View 7 Replies View Related

DB Engine :: Splitting Data Of Single Large MDF File

Nov 13, 2015

We have a 5 TB database in our environment. Both MDF & LDF are location in 1 single drive which is of 10 TB.

Now, we want to move to new server but we have multiple drives each of max 1 TB per drive. How can I go about splitting the data from 1 MDF files into multiple data files? How about moving indexes ?

SQL Version : Microsoft SQL Server 2012 (SP1) - 11.0.3513.0 (X64) - Enterprise Edition (64-bit)

View 10 Replies View Related

How To Display Multiple Rows Of A Table In Single Row

Dec 15, 2007

DECLARE @emp VARCHAR(1024) declare @emp1 varchar(1024)declare @emp2 varchar(1024)SELECT @emp1 = COALESCE(@emp1 + ',', '') + cast(eid as varchar(10)),@emp = COALESCE(@emp + ',', '') + ename ,@emp2 = COALESCE(@emp2 + ',', '') + desigFROM emp SELECT eid=@emp1,ename = @emp,desig=@emp2

View 1 Replies View Related

Deleting Duplicate Rows Within A Single Table

May 14, 2004

I was wondering if anyone had a suggestion as to how to delete duplicate rows from a table. I have been doing this:

SELECT * INTO TempUsersNoRepeats
FROM TempUsers2
UNION
SELECT * FROM TempUsers3

This way I end up with a total of four tables (the fourth table being the original Users table) and I was hoping that there was a way that I could do this all within the the original Users table and not have to create the three TempUsers tables.

Thanks,
Ron

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

Query On Multiple Rows In Single Table?

Aug 1, 2014

I have a table named LEDGER

LEDGER has two columns named PATID and CODE

Example data:

PATID CODE
1 Z1110
1 D3330
1 Z0330
2 Z1298
2 Z0987
2 Z0330
2 D1092

I need a query that returns PATID if they have CODE Z0330 but not Z1110. I only want one PATID to return if this condition exists.

View 5 Replies View Related

Single Row Result From Mutliple Table Rows?

Sep 19, 2007

View 8 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 2000: Inserting Multiple Rows Into A Single Table

Jul 20, 2005

To anyone that is able to help....What I am trying to do is this. I have two tables (Orders, andOrderDetails), and my question is on the order details. I would liketo set up a stored procedure that essentially inserts in the orderstable the mail order, and then insert multiple orderdetails within thesame transaction. I also need to do this via SQL 2000. Right now ihave "x" amount of variables for all columns in my orders tables, andall Columns in my Order Details table. I.e. @OColumn1, @OColumn2,@OColumn3, @ODColumn1, @ODColumn2, etc... I would like to create astored procedure to insert into Orders, and have that call anotherstored procedure to insert all the Order details associated with thatorder. The only way I can think of doing it is for the program to passme a string of data per column for order details, and parse the stringvia T-SQL. I would like to get away from the String format, and gowith something else. If possible I would like the application tosubmit a single value per variable multiple times. If I do it this waythough it will be running the entire SP again, and again. Anysuggestions on the best way to solve this would be greatlyappreciated. If anyone can come up with a better way feel free. Myonly requirement is that it be done in SQL.Thank you

View 3 Replies View Related

Comparing Data In Two Consecutive Rows From A Single Table

Jul 20, 2005

I'm trying to come up with an elegant, simple way to compare twoconsecutive values from the same table.For instance:SELECT TOP 2 datavalues FROM myTable ORDER BY timestamp DESCThat gives me the two latest values. I want to test the rate ofchange of these values. If the top row is a 50% increase over the rowbelow it, I'll execute some special logic.What are my options? The only ways I can think of doing this arepretty ugly. Any help is very much appreciated. Thanks!B.

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

SQL 2000 How To Insert Multiple Rows Ina Single Table

Oct 25, 2007

I am using SQL 2000
I am getting a syntax error when I parse this sql script:

insert into Elec_Sub_Test1
values ('10-20-2007',35),
('10-21-2007',24)

What is the correct syntax to insert mutlipe rows in a single table.

View 4 Replies View Related

SQL Server 2012 :: Merge Multiple Rows From Single Table

Nov 17, 2014

I have resulting rows from a query similar to the following:

The data is coming from a single table that contains only one coverage code column and one coverage code date, but the end user wants the two coverage code types and dates combined into a single row. So the SELECT looks something like this:

SELECT
[Employee ID] = emp.employee_id,
[Coverage Code 1] = enr.coverage_code,
[Coverage Date 1] = enr.coverage_date,
[Coverage Code 2] = case when enr.product_type = 'Accident.Accident'
then enr.coverage_code else NULL end,

[Code] ....

I basically want to merge the like Employee ID's together into a single row like the following:

I know I have done this before and it is probably pretty simple.

View 4 Replies View Related

Transact SQL :: How To Update Multiple Rows In Different Transactions In A Single Table

Jul 16, 2015

We have control table which will be useful whether we need to start the job or not. If we are starting the Job we will make it to 1.

Below is the Table Structure.

Table Name IN_USE_FG
CUST_D 0
PROD_D 0
GEO_D 0
DATE_D 0

Now we have different packages for 4 tables data loading. These 4 packages will start at a time. Before going to load the data we have to make the Flag to 1 and after that we have to load it. Because of this we have written Update statement to update the Value to 1 in respective Package.

Now we are getting dead lock because we are using same table at a same time. Because we are updating different records.

View 6 Replies View Related

SQL Server 2012 :: Insert Multiple Rows In A Table With A Single Select Statement?

Feb 12, 2014

I have created a trigger that is set off every time a new item has been added to TableA.The trigger then inserts 4 rows into TableB that contains two columns (item, task type).

Each row will have the same item, but with a different task type.ie.

TableA.item, 'Planning'
TableA.item, 'Design'
TableA.item, 'Program'
TableA.item, 'Production'

How can I do this with tSQL using a single select statement?

View 6 Replies View Related

T-SQL (SS2K8) :: Splitting Row Into Multiple Rows

May 14, 2015

I have a table that has for example data

I am looking to write a script that will change the first table into the second table.......

Table 1

Account No Name other field
1 Mr T and Mrs M Lambert xxx

I need to rewrite this as

Table 2

Account No split Name other field
1 a Mr T Lambert xxx
1 b Mrs M Lambert xxx

View 9 Replies View Related

Splitting A Comma Seperated Column Into Multiple Rows

Jan 18, 2006

Hi i want to create a table as follows :if exists (select * from dbo.sysobjects where id =object_id(N'[Indexes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [Indexes]GOCreate table Indexes(indexname Varchar(100), index_DescriptionVarchar(100), index_keys Varchar(100))GOINSERT INTO Indexes EXEC sp_helpindex 'SDM_Fact_Order_Detail'GOThis will give me a table (northwind)IX_Auto_SDM_Fact_FK_Shipped_Date nonclustered located onSAMIS_SDM_Index FK_Shipped_DateIX_Auto_SDM_Fact_Order_Detail_FK_Insert_Dateclustered located onSAMIS_SDM_Data1FK_Insert_Date, FK_Insert_TimeAs you see sp_helpindex will give me a comma seperated field. I wantto split the third column FK_Insert_Date, FK_Insert_Time into a extrarowLike this :IX_Auto_SDM_Fact_FK_Shipped_Date FK_Shipped_DateIX_Auto_SDM_Fact_Order_Detail_FK_Insert_Date FK_Insert_DateIX_Auto_SDM_Fact_Order_Detail_FK_Insert_Date FK_Insert_TimeCan anyone help me with this?ThanxHennie

View 1 Replies View Related

Reporting Services :: Splitting Data In Tablix On Two Or More Rows

Jun 17, 2015

I have a report I'm designing where, as a simple SQL report viewed only on a screen, it was irrelevant how wide it was. However, now I've been asked to duplicate this report in SSRS and to include the option to print it out.

Well, the problem is, as it stands - with 8pt font, even - it will require a sheet of paper about 24" wide to get all of a single row to print.

So, I'm trying to create a Tablix that will split the data into two sets of header/detail rows in the same Tablix. Any workable solution that doesn't involve writing an app in basic or C.

View 10 Replies View Related

Transact SQL :: Converting From Multiple Rows With Single Values To Single Rows With Multiple Values

May 10, 2015

Here is some data that will explain what I want to do:

Input Data:
Part ColorCode
A100 123
A100 456
A100 789
B100 456
C100 123
C100 456

Output Data:
Part ColorCode
A100 123;456;789
B100 456
C100 123;456

View 4 Replies View Related

Splitting A Table.

Apr 27, 2006

I need to split a field that contains a 4 digit number, into two tables each containing a 2 digit number. This is for an SSIS package to import data from a text file.

Thanks folks.

View 1 Replies View Related

Splitting A Table In Half

Jul 30, 2007

Hi all,
I am trying to create a table of counts but it is running too long and would be a waste of paper so in essence I want the table to wrap at the 50% mark. As far as I understand theres no way to do that with just one table in RS so Im planning on using two tables and filtering them somehow.

I have been looking at the top % and bottom % filters but they dont seem to work for my purposes.

Heres what I got:

Program
Count

Program 1
40

Program 2
25

Program 3
23

Program 4
18

Program 5
10

Program 6
5

Program 7
1
Heres what I want to do:

Program
Count

Program 1
40

Program 2
25

Program 3
23

Program 4
18

Program
Count

Program 5
10

Program 6
5

Program 7
1

gte 50%
lt 50%
Does anyone know an easy way to split a data set by group at the 50% mark?

Thanks!

View 9 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 A Composite Primary Key In A Table

Mar 26, 2008

NOTE:
I am not interested in any responses that want to argue the use of a unique ID field and autonumbering as the PK. It is quite clear from the forums that this subject is a polarizing one. This question is for those who follow text-book design practices and believe that a composite primary key should be used when it is available. I want to be one of them for the time being.

SYNOPSIS:
I have three tables, TestSummary, TestDetails, and Steps.

The TestSummary table looks like this:

Create table TestSummary
(
TestSummaryID int identity primary key,
...
SequenceID int not null
)

It contains the date and time of the test, the serial number, the part number, the test operator's name, and the ID of the sequence of steps used during the test. It uses a unique ID field for the primary key.

The TestDetails table looks like this:

Create table TestDetails
(
TestDetailsID int identity primary key,
TestSummaryID int not null,
StepID int not null,
...
)

It contains the details of the test like voltage readings, current readings, temperature, etc., one record per reading. It also contains the step number of the test sequence specified in the TestSummary table.

The Steps table looks like this:

Create table Steps
(
SequenceID int not null,
StepID int not null,
Function int not null
Primary key (SequenceID, StepID)
)

It contains a list of all of the functions to be performed on the device under test by sequence number and the step number within the sequence.

When I try to establish a relation between TestSummary.SequenceID and Steps.SequenceID, SQL Server flags an error because TestSummary.SequenceID and Steps.SequenceID:Steps.StepID do not match.

What is the problem with this approach?

View 1 Replies View Related

Combine Data In Single Row From Single Table

Apr 4, 2006

How can i combine my data in single row ? All data are in a single table sorted as employeeno, date

Code:

Employee No Date SALARY
1 10/30/2006 500
1 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000
4 11/01/2006 8000

Should Appear

Code:

EmployeeNo Date1 OLDSALARY Date2 NEWSALARY
1 10/30/2006 500 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000 11/01/2006 800

PLEASE HELP I REALLY NEED THE RIGHT QUERY FOR THIS OUTPUT.

THANKS IN ADVANCE

View 3 Replies View Related

Merging Two Rows Into A Single One

Mar 5, 2012

I'm using a shipping program called endicia professional that allows for database manipulation to make my processing easier. I've managed to fix the database here and there but have had an issue combining orders from a single customer when theybuy more than one item. Ideally I would like to have it combine rows when a customer purchases items going to the same address. To avoid having an issue where the address line is the same ie two people live in the same appt complex and it combines these I thought we could use qualifiers as the purchase will have name, order Id that should be unique enough

Order-id name address sku
1234 John 46 easy ln. A27
1234 John 46 easy ln. B32

Results:
Order-id name address sku
1234 John 46 easy ln. A27,b32

View 6 Replies View Related

How To Get All Same Rows In Single Line

Mar 6, 2013

i have a query in which i need to show all rows of same id in singel row and last column should be comma seperated .my query is :

BEGIN TRY
SET NOCOUNT ON
DECLARE @l_numberOfRecordsInserted INT
-- Check if valid Application User ID is passed
IF ( @i_AppUserId IS NULL ) OR ( @i_AppUserId <= 0 )

[code]....

so last column section name should come as comma seperated .

View 3 Replies View Related

Single Row Into Multiple Rows

May 8, 2008

Hi All,

We've a table as in the following format:

PK_Column1
PK_Column2
Issue_Date1
Issue_Amount1
Issue_Category1
Issue_Reject1
Issue_Date2
Issue_Amount2
Issue_Category2
Issue_Reject2

We need to divide it into two new tables as follows:

UniqueID
PK_Column1
PK_Column2
And

UniqueID
PK_Column1
PK_Column2
Sequence_ID
Issue_Date
Issue_Amount
Issue_Category
Issue_Reject

Unique1

1
Issue_Date1
Issue_Amount1
Issue_Category1
Issue_Reject1

Unique2

2
Issue_Date2
Issue_Amount2
Issue_Category2

Unique3

1
xx

xx

Unique4

2
xx

xx

Unique5

3
xx

4

xx

There will be one UniqueID for each row.
We'll get the uniqueID and PK1 and PK2 in a file.
Imp: We need to generate the Sequence_Id depending on number of Issue_dates or Issue_amounts or Issue_Categories or Issue_Rejects as in the above table.

Can we do this without using cursors?
This is going to be one time process.

Any ideas are appreciated.

Thanks,
Siva.

View 1 Replies View Related

Getting Multiple Rows In A Single Row

Jan 21, 2008

Hi,

I've a temp variable where I'm moving some columns like below:

id
value
type1
type2

0
ab
type1val1
type2val1

0
cd
type1val1
type2val1

0
ef
type1val1
type2val1

1
ab
type1val2
type2val2

1
cd
type1val2
type2val2

1
ef
type1val2
type2val2
What I want to do is group these by their id and get the following o/p
ab,cd,ef type1val1 type2val1
ab,cd,ef type1val2 type2val2

The grouped values need to be separated by commas.

What I'm doing currently:
I'm using a temp variable to put all these values but am unable to coalesce and get the desired o/p.

Can anybody help me out?

Thanks,
Subha

View 4 Replies View Related

Spliting Single Row Into Multiple Rows

Sep 20, 2006

I have a table that contains many columns in a single row and I'd like to split the table so that it has fewer column values and more rows.
My table structure is:
create table #scoresheet
(Decisions varchar(10), DNumericalValue int, DVI varchar(10), DComments nvarchar(255),
Competence varchar(10), CNumericalValue int, CVI varchar(10), CComments nvarchar(255),
Equipment varchar(10), ENumericalValue int, EVI varchar(10), EComments nvarchar(255));
I would like to have three rows with four columns.
What I've done so far is create a stored procedure that uses a table variable:
create procedure sp_splitsinglerow as

declare @Scoresheet_rows_table_var table (
ReviewArea varchar(25),
NumericalValue int,
VI varchar(10),
Comments nvarchar(255));
insert into @Scoresheet_rows_table_var
(ReviewArea, NumericalValue, VI, Comments)
select Decisions, DNumericalValue, DVI, DComments
from #scoresheet

The trouble with this approach is that I have to explicitly name the columns that I insert into the table variable. What I'd really like to be able to is have a loop construct and select the first 4 columns the first time, the second 4 the next time and the last 4 the third time.

Any ideas on how to achieve that?

BTW, I have resolved this issue by suggesting to the Developers that they change the structure of the original table, but I'd still like to know if there is another solution. :)

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

T-SQL (SS2K8) :: Multiple Rows Into A Single Row

Nov 7, 2014

I am working with some old code that we are trying to clean up and perform some performance enhancements. The performance is now, so Very much better. From over 3 minutes to under 2 seconds.

But I am still trying to get the multiple rows into a single row. I would like to place this into a CTE to get the multiples into a single row. I just cannot get my head around how is the best, most efficient way to write the query.

This is a small example of what the rows look like in the resultset, and what I want to single to be.

DECLARE @BillingCorrect TABLE
(
ContractNumber char(10)
, pc1 int
, pb int
, om int
, vp int

[Code] ....

I am not sure how to write the query to have all the data in a single row.

View 2 Replies View Related

Display 20 Rows At Single Time

Sep 25, 2015

The stored procedure takes ~ 10 seconds every call. The execution plan is Index seek or clustered index scan almost everywhere . No table spools or lazy loads or key lookups. Cannot share the code or execution plan due to company rules and regulations.Issue is that i am dealing with result set of around 50,000 records . Out of these i have to return 20 records at a single time (which is also customizable i.e 40 / 60/ 150 records in a page). Application cannot handle all 50k records so i have to return 20 records for every stored procedure call.

The result set changes as per the start date and end date which i recieve as parameters. In application there are few Column filters namely- Country(around 50 countries), Outcome(around 6 to 10 values ) . These filters will values in drop down( as excel ) depending on the distinct values in that columns. These filters will be populated on every page, if no filter value is selected.Issue is if user does sorting or filtering any records , this stored procedure is called and every time i have to deal with ~50000 records.

Current Code :
Step 1 ) Get the required result set in temp table .
Step 2) Compute the results on some business rules . (Outcome and SharesAvailable calculation - see attachment)
Step 3) populate filter Columns (Country,Outcome) these values will be comma seperated.
Step 4) Dynamic query to get required result set i.e if user wants only 10 records in single page then TOP 10 . Sorting can be applied on any column mentioned in screenshot.

View 9 Replies View Related