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.
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)
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)
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.
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.
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
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.
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 {
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.
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.
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.
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
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.
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.
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?
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.
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
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 .
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.
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.
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. :)
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.
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.
