How To Split Data Into Two Rows
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 Complete Forum Thread with Replies
Related Forum Messages:
Split Rows
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 Replies !
Can I Split Matrix Into 2 Or 3 Rows?
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 Replies !
How To Split Out Table Rows Into 3 Tables
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 Replies !
Split One Field In Multiple New Rows
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 Replies !
How Can I Split The Rows In 2 Textboxes - Urgent
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 Replies !
One For The SQL Gurus: Split A Delimited Field Into Rows
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 Replies !
SQL View To Split Rows In Single Table...
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 Replies !
How To Assign Unique PKs And FKs On Split Of Txt Rows Into DB Tables
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 Replies !
Split One Row Into Multiple Rows Based On Time Elements
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 Replies !
How To Split A Delimited Column Into Mulitple Rows In The Dataflow?
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 Replies !
Transform To Remove Rows From Data Set A That Match Rows In Data Set B On A Given Key?
Hi, I have a common requirement in numerous SSIS processes to take my main input data set and to remove all rows from it that match a second input data set on a given key and output this as the main output. I also want to output (as a second output) all the rows from the main input data set that did match on the given key. However, I don't want to merge in data from the second input, nor am I interested in rows from the second input data set that have no match in the main input. E.g. If I have the following data: Main input: Key Name --- ---- 1 Steve 2 Jamie 3 Donald Second Input Key DontCareAboutThisField1 --- ----------------------- 1 ... 3 ... 4 ... Then I would like the following output: Main Output Key Name --- ---- 2 Jamie Second Output Key Name --- ---- 1 Steve 3 Donald Can I do this with a standard transform, or will I have to write my own? Any help on this would be greatly appreciated! Thanks in advance, Lawrie
View Replies !
Split Data
My company use SQL server 2005 standard. considering deal with huge data, how if we want to split data (date range yearly or monthly) in order to ease transaction. that's simply for us to use query, but how if we want to split data that can be easily execute by operator (non-admin privilege). Is there any another way?
View Replies !
Data Flow Split
I'm trying to figure out how in a Data Flow Transform I can split some data. I have data coming through that has a PK (col1) and datetime col (col2). This data may contain multiple rows for the PK, col1. I want to be able to take the min datetime for each row for col1 and send down one path and all other rows down another path eg. There are 20 rows in the Data Flow coming through. Col1 has value 1 for first 10 rows, value 2 for remaining 10 rows and there are different values for Col2 for all rows. Take the Min value in Col2, where col1=1 and also the min value in Col2, where col1=2 and send down one path. Send all other rows down another path. I thought of using the Conditional Split Transform but my Expression knowledge isin't experienced enough (I'm looking into this) and I'm not sure if it can be done. I also though of using the Multi-Cast Transform and then using the Aggregate function, on each data set. While I can do this easily for the first data flow. SELECT col1, MIN(col2) FROM tbl1 GROUP BY col1 which is easily done in the Aggregate transform, the other side of the data flow I can't see how can be done using the Aggregate Transform SELECT col1 FROM tbl1 WHERE col2 NOT IN (SELECT MIN(col2) FROM tbl1 GROUP BY col1). Are either methods feasilble or is there another way. I want to avoid putting this data into temp tables in a SQL database and manipulating the data from there. The data has been extracted from a flat file source. Any help and ideas welcome.
View Replies !
Split The Data Into Columns
I have a table called products with the values like ProductId ProductName 10 A 20 D,E,F,G 30 B,C 40 H,I,J I need to display each productid's with ProductId ProductName 10 A 20 D 20 E 20 F 20 G 30 B 30 C 40 H 40 I 40 J I will be appreciated if you can send me the code. Thanks, Mears
View Replies !
Split Data In Column
hai all, This is my first question to this forum. here is my situtation: I am into report testing I need to test a report for which i have write a query,iam using qery analyser for runing query Database : sql server tabel name :job_allocations column naME :technicain code Based on techincain code in joballocation tablei need to get technician cost from other table for the particular technician. Based on the technician code user chooses column will be updated if single data will be TC01 if more than one then data will be TC01:TC02:TC03 user can choose any number of techincian for a job MY problem is :How to split tha when there is multiple technician and calculate cost for the job Ineed it in single excecution query Table structure job_allocation table jobcardn_fk Technician_code jc01 TC01 jc02 Tco1:Tco2:Tc03...... I need it in jobcardno_fk TEchnician_code jco1 Tc01 jco2 Tc01 jco2 TC02 jc02 Tc03 TKs ands Regards Diwakar.R
View Replies !
T-SQL To Split Data From One Table Into Two Tables?
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 Replies !
Using Condation Split To Update Data
hi all i using SSIS to update data on tables based on another tables as follow start connect to DS --> using Lookup to located the key fields on the 2 tables --> on error start insert into the second table else if the recorde is already exist start update my problem is Performance --source table rows count almost 60 million --distination table almost 3 million my package execute from 2 days and still working till now updated 122,000 and insert 4 million kindly i need support thanks & regards
View Replies !
Split Data Into Two Column Table
Hello all, Little layout question. Assume my dataset returns the following data: A B C D E How can I present this data in a table (or list, or matrix) splitted into two columns: A B C D E Any idea will be very appreciated! Thanks a lot! TG
View Replies !
How To Split Data From The MS SQL 2000 Database?
Hi All, We are working on a project, (C#.Net 2003, MS SQL 2000) where database is growing as 2 to 3 GB per day (Scanned Documents are storing in Database). There is only one table in the database. Currently database size is grown upto 200 GB. For safety reason we are planning to split the database accoring to one key field in the table say Book_no. Please guide/suggest me how to split the database now on the SQL query like "SELECT * FROM master_records WHERE book_no=1" And later how to merge/combine all these splitted database into one. I thought of using "Data Export" and later "Import with Append" but don't know whethere it will effective or not? Any method or tool available to Split the database table and later combine them to make again a single one? Please help me, its urgent. Thanks in advance.
View Replies !
Importing Split Data Into Table
Hi, I have Data split into 3 text files with 3 fields repeated in each to link then (key). I want to import this data into one table. I used DTS to create 3 tables with the data. Now i want to combine the 3 tables into only one (that i already created). How can i do this? Note: the field names in the source tables are different from the destination table. Thanks Guy
View Replies !
Arranging Data On Multiple Rows Into A Sigle Row (converting Rows Into Columns)
Hello, I have a survey (30 questions) application in a SQL server db. The application uses several relational tables. The results are arranged so that each answer is on a seperate row: user1 answer1user1 answer2user1 answer3user2 answer1user2 answer2user2 answer3 For statistical analysis I need to transfer the results to an Excel spreadsheet (for later use in SPSS). In the spreadsheet I need the results to appear so that each user will be on a single row with all of that user's answers on that single row (A column for each answer): user1 answer1 answer2 answer3user2 answer1 answer2 answer3 How can this be done? How can all answers of a user appear on a single row Thanx,Danny.
View Replies !
Using SSIS 2005 To Strip Out Bad Rows In Excel And Then Insert Detailed Rows Into OLE DB Data Source
Environment: Running this code on my PC via VS 2005 .Net version 2.0.50727 on the server (shown in IIS) Code is in ASP.NET 2.0 and is a VB.NET Console application SSIS 2005 Problem & Info: I am bringing in an Excel file. I need to first strip out any non-detail rows such as the breaks you see with totals and what not. I should in the end have only detail rows left before I start moving them into my SQL Table. I'm not sure how to first strip this information out in SSIS specfically how down to the right component and how to actually code the component to do this based on my Excel file here: http://www.webfound.net/excelfile.xls Then, I assume I just use a Flat File Source coponent or something to actually take the columns in the Excel and split into an OLE DB Datasource to shove each column into a corresponding column in my SQL Server Table. I have used a Flat File Source in the past to do so with a comma delimited txt file but never tried with an Excel. Desired Help: How to perform 1) stripping out all undesired rows 2) importing each column into sql table
View Replies !
Split A Single Column Data In To 2 Columns
Hi This is probably a very basic question for most people in this group. How do i split the data in a column in to 2 columns? This can be done in access with an update query but in MS SQL server I am not sure. Here is an example of what i want to acheive FName John?Doe FName LName John Doe thanks for the help prit
View Replies !
Split Column Data Into Multiple Lines
Hi, I have a scenario, where I have a string column from database with value as "FTW*Christopher,Lawson|FTW*Bradley,James". In my report, I need to split this column at each " | " symbol and place each substring one below the other in one row of a report as shown below . "FTW*Christopher,Lawson FTW*Bradley,James" Please let me know how can I acheive this?
View Replies !
Retrieving Data From A DB Based On Output Of A Conditional Split
This is probably an easy question, and I just can't find the solution. I've searched extensively, but I am probably just not searching for exactly what I need. Basically, I have a Conditional Split. What I need to do is for each row coming out of my split, I need to SELECT some data from another database based on one of the fields and then place the data from the DB into a file for later processing. Seems pretty simple, considering the power of SSIS. Using tools such as OLE DB Command didn't help - the data that comes out of the OLE DB Command is the input data, not the data returned by the command. How can I do this? Thank you! Nolan
View Replies !
Deadlock Problem? 3 Way Conditional Split Of Data From One Table To Another Never Completes
I have a source table which I'm splitting 3 ways based on a column value, but the target is the same OLE DB destination table. One conditional path is to a Multi-Cast two way split to same OLE DB gestination table. The default split is to a flat file for logging unknown record types. For a test I have data for only the 3 column values I want, but I'm having trouble with the process completing. If I pre-filter the data going into the source table by one or two values I can get the process to complete even if one split is to the multicast. If I include all three data types in the source table, I get different results depending on the order in which the conditions are specified - sometimes only two split paths are executed; other times all three are executed, but in some cases only one path of the multicast split is executed. In any case, when the three source data types are used in the test, the process never competes - the pathes are in a yellow condition and never complete. Am I creating some kind of deadlock situation by having the source data directed to the same target table via 4 splits? Any help you can provide is appreciated. Thanks.
View Replies !
Split DB - Static Controls Versus User Data - Feedback On Plan
In my prototype, I've got i) the (relatively) static control files (various control values) and ii) the user datain the same DB (SQL Server Express). Both types of files are entirely accessed through table adapters. I'm thinking I'd like to split the data into two databases, one for just the static control files, and the other for the user data. Then split the app into two separate apps, one for user data, one for just the control tables. I'm thinking this will be more secure, easier to maintain and rebuild if something fails, other reasons (...?) I'm hoping to get some general feedback on the reasonability of doing this, and on my general approach. My first approximation of a plan to do this would be:0) lot's of backups first and during!A) Copy the current DB, rename as "Control-something, delete the "user" tables from it, then delete the control tables from the original "Data" DB B) similarly copy, rename, split the app files into "control" and "user" appsC) in the both of the resulting apps 1) create a new connection string to the "new" control DB 2) Edit the tableadapters as necessary to use the new connection string D) Figure out where there needs to be any "cross over" links between the two apps (dunno right now, users won't touch app controls, though maybe some administrators would....) E) Plan to deploy both DB's and both apps as appropriate, with separate access URL's for the two different apps. So the questions are 0) Any real advantages to doing this, for security, maintenance, etc?1) Is this a "commonly used" approach, or do most people just keep the data and controls in one DB?2) Any issues with one app / multiple DB;s (it's going to be a single server app for the first release.)3) Any issues with simultaneously accessing one DB from two apps?4) Are there any big missing pieces in this conversion plan? Any feedback on this would be appreciated. Thanks!
View Replies !
Combine Data And Split Into Separate Txt Files For Each Header/detail Row Groupings
I€™ve created with the help of some great people an SSIS 2005 package which does the follow so far: 1) Takes an incoming txt file. Example txt file: http://www.webfound.net/split.txt The txt file going from top to bottom is sort of grouped like this Header Row (designated by €˜HD€™) Corresponding Detail Rows for the Header Row €¦.. Next Header Row Corresponding Detail Rows €¦and so on http://www.webfound.net/rows.jpg 2) Header Rows are split into one table, Maintenance Detail Rows into another, and Payment Detail Rows into a third table. A uniqueID has been created for each header and it€™s related detail rows to form a PK/FK relationship as there was non prior to the import, only the relation was in order of header / related rows below it when we first started. The reason I split this out is so I can massage it later with stored proc filters, whatever€¦ Now I€™m trying to somehow bring back the data in those table together like it was initially using a query so that I can cut out each of the Header / Detail Row sections into their own txt file. So, if you look at the original txt file, each new header and it€™s related detail rows (example of a cut piece would be http://www.webfound.net/rows.jpg) need to be cut out and put into their own separate txt file. This is where I€™m stuck. How to create a query to combine it all back into an OLE DB Souce component, then somehow read that souce and split out the sections into their own individual txt files. The filenames of the txt files will vary and be based on one of the column values already in the header table. Here is a print screen of my package so far: http://www.webfound.net/tasks.jpg http://www.webfound.net/Import_MaintenanceFile_Task_components.jpg http://www.webfound.net/DataFlow_Task_components.jpg Let me know if you need more info. Examples of the actual data in the tables are here: http://www.webfound.net/mnt_headerRows.txt http://www.webfound.net/mnt_MaintenanceRows.txt http://www.webfound.net/mnt_PaymentRows.txt Here's a print screen of the table schema: http://www.webfound.net/schema.jpg
View Replies !
Return Two Rows From One Rows Data
I know this table is designed wrong for what I am doing but I hope Ican do it. I have a table like this.Prod_A_Jan, Prod_A_Feb, Prod_B_Jan, Prod_B_FebI want a query that returns data like this (two rows of data)"ProdA", Prod_A_Jan, Prod_A_Feb"ProdB", Prod_B_Jan, Prod_B_FebI know two queries can get it but I want one. Any Help would begreat!!!Sheila T.
View Replies !
Moving Files (split From An Existing Thread-SSIS Equivalent To DTS Transform Data Task Properties)
Hi JayH (or anyone). Another week...a new set of problems. I obviously need to learn .net syntax, but because of project deadlines in converting from DTS to SSIS it is hard for me to stop and do that. So, if someone could help me some easy syntax, I would really appreciate it. In DTS, there was a VBScript that copied a set of flat files from one directory to an archive directory after modifying the file name. In SSIS, the directory and archive directory will be specified in the config file. So, I need a .net script that retrieves a file, renames it and copies it to a different directory. Linda Here is the old VBScript Code: Public Sub Main() Option Explicit Function Main() Dim MovementDataDir Dim MovementArchiveDataDir Dim MovementDataFile Dim MovementArchiveDataFile Dim FileNameRoot Dim FileNameExtension, DecimalLocation Dim CurMonth, CurDay Dim FileApplicationDate Dim fso ' File System Object Dim folder Dim FileCollection Dim MovementFile '====================================================================== 'Create text strings of today's date to be appended to the archived file. FileApplicationDate = Now CurMonth = Month(FileApplicationDate) CurDay = Day(FileApplicationDate) If Len(CurMonth) = 1 Then CurMonth = "0" & CurMonth End If If Len(CurDay) = 1 Then CurDay = "0" & CurDay End If FileApplicationDate = CurMonth & CurDay & Year(FileApplicationDate) '===================================================================== ' Set the movement data directory from the global variable. MovementDataDir = DTSGlobalVariables("gsMovementDataDir").Value MovementArchiveDataDir = DTSGlobalVariables("gsMovementDataArchiveDir").Value fso = CreateObject("Scripting.FileSystemObject") folder = fso.GetFolder(MovementDataDir) FileCollection = folder.Files ' Loop through all files in the data directory. For Each MovementFile In FileCollection ' Get the full path name of the current data file. MovementDataFile = MovementDataDir & "" & MovementFile.Name ' Get the full path name of the archive data file. MovementArchiveDataFile = MovementArchiveDataDir & "" & MovementFile.Name DecimalLocation = InStr(1, MovementArchiveDataFile, ".") FileNameExtension = Mid(MovementArchiveDataFile, DecimalLocation, Len(MovementArchiveDataFile) - DecimalLocation + 1) FileNameRoot = Mid(MovementArchiveDataFile, 1, DecimalLocation - 1) MovementArchiveDataFile = FileNameRoot & "_" & FileApplicationDate & FileNameExtension If (fso.FileExists(MovementDataFile)) Then fso.CopyFile(MovementDataFile, MovementArchiveDataFile) ' If the archive file was coppied, then delete the old copy. If (fso.FileExists(MovementArchiveDataFile)) Then fso.DeleteFile(MovementDataFile) End If End If Next fso = Nothing folder = Nothing FileCollection = Nothing Main = DTSTaskExecResult_Success End Function
View Replies !
Delete Rows With Duplicate Column Data But Unique Row Data
Hello, This probably has been addressed before but I was unable to get the search to work properly on this site. I am needing a script/way of deleting all rows from a DB with the exception of one record left for each row that has duplicate column data. Example : Row 1 Field1 = 12345 Field2 =xxxxx Field 3=yyyyy Field4=zzzzz etc. Row 2 Field1 = 12345 Field2 =zzzzzz Field 3=xxxxxx Field4=yyyyyy etc. Row3 Field1 = 12345 Field2 =20202 Field 3=11111 Field4=zzzzz etc. Row 4 Field1 = 54321 Field2 =xxxxx Field 3=yyyyy Field4=zzzzz etc. Etc. Etc. I want to be able to find the duplicates for Field1 and then delete all but 1 of those rows.( I don't care which one I keep just so only one is left.) The data in the other fields may or may not be unique. I know how to find the duplicates it's just the deleting part I am having problems with. Any help would be much appreciated. Thanks, Kerry
View Replies !
Split Data File Into Multiple File Groups..!
I have one of our production Accounting Databases starting from 2 GBnow grown into a 20 GB Database over the period of a few years...I have been getting timeouts when transactions are trying to updatedifferent tables in the database.. Most of the error I get are I/Orequests to the data file (Data file of the production dbAccounting_Data.MDF).I would like to implement the following to this Accounting database.I need to split the Data file into multiple files by placing some ofthe tables in different file groups. I have the server upgraded to beable to have different drives in different channels. I can place thesedata and log files in different drives so it will be less I/Oconflicts..I would like to have the following file groups..FileGroup 1 - which will have all database definitions (DDL).FileGroup 2 - I will have the AR Module tables under here..FileGroup 3 - I will have the GL module tables under here..FileGroup 4 - I will have the rest of the tables under hereFileGroup 5 - I will like to place the indexes under here....Also where will the associated transaction files go?I would like to get some help doing this. Is there any articles / helpavailable that I can refer to. Any suggestions / corrections/criticisms to what I have mentioned above is much appreciated...!Thanks in advance....
View Replies !
Several Rows With The Same Data Into 1 Row
There is table where some rows includes the same data (at least UserId, Name but ID - unique) ID, UserID, Name, Money Required, Money Spent, Products 1 234 aaa 2000 345 Product1 2 234 aaa 300 279 Product2 3 122 bbb 10000 987 Product 333 How can i get the next data without using sql: ID, UserID, Name, Money Required, Money Spent, Products 1 234 aaa 2300 624 Product1, Product2 3 122 bbb 10000 987 Product 333 As u can see i add data from several rows into 1 row when UserID is the same (as numbers so and text) in appropriate collumns. Thanx in advance for reply.
View Replies !
First N Rows Of Data
This is for you Kristen!! Instrucions! -- Change current db (in blue) -- Change source table (in red) -- Change sample rows (in red) -- run to generate insert statements Use northwind Declare @dbName varchar(100), @tableName varchar(100), @seedCnt int Set @tableName = 'customers' Set @seedCnt = 50 Declare @execStr varchar(8000) --If there is an image column in the specified table, display warning if exists(Select * From Information_Schema.columns Where table_Name = @tablename and data_type='image') Select 'Image Type not supported' --If there is an ntext column in the specified table, display warning if exists(Select * From Information_Schema.columns Where table_Name = @tablename and data_type='ntext') Select 'nText Type not supported' --build column translations Select @execStr = isnull(@execStr + ' +' + char(13) + char(10),'') + case when data_type in ('uniqueidentifier') then char(9) + '''' + column_name + '='' + isnull('''''''' + convert(varchar(50),' + column_Name + ') + '''''''',''null'') + '', ''' when data_type in ('char','nchar','varchar', 'nvarchar') then char(9) + '''' + column_name + '='' + isnull('''''''' + replace(' + column_Name + ','''''''','''''''''''') + '''''''',''null'') + '', ''' when data_type in ('datetime') then char(9) + '''' + column_name + '='' + isnull('''''''' + convert(varchar,' + column_Name + ',121) + '''''''',''null'') + '', ''' when data_type in ('int','float','decimal','money','bit','smallint','real','bigint') then char(9) + '''' + column_name + '='' + isnull(convert(varchar,' + column_Name + '),''null'') + '', ''' end From Information_Schema.columns Where table_Name = @tablename and data_type not in ('image','ntext') --and Ordinal_Position<=1 --Select @execStr Select @execStr=left(@execStr,len(@execStr)-4) + '''''' Select @execStr = 'Select top ' + convert(varchar,@seedCnt) + char(13) + char(10) + '''Insert Into #' + replace(@tableName,' ','') + ' Select '' + ' + char(13) + char(10) + @execStr + char(13) + char(10) + 'from [' + @tableName + ']' --Select @execStr Exec(@execStr) viola! Corey Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."
View Replies !
500 Million Rows Of Data?
I'm new to using a DB and have a few questions about what I'm trying to do. I have some historical options data and want to place it into a sql express database. (I understand I might need to use a none express version once the db gets to big.) A months worth of data is over 5.5 million rows of data. So six years worth is ~400 million rows. Is it possible to put this into a sql db and be able to search it very fast? I have a months worth in a db now and it is pretty slow. Should I use a new table for each month and then have 6 years * 12 month = 72 tables to increase the search speed? I search by date and stock_symbol and the data looks like this: Date, Stock_Symbol, Option_Symbol, Strike, BidPrice, AskPrice, Volume, OpenInterest, (and a few others) The select statement is simple: SELECT * FROM Options WHERE Date = @Date and StockSymbol = @Symbol Thanks
View Replies !
How Can I Store Data In One Row Instead Of 10 Rows?
Hi I have this stored procedure using Table Variable. ------------------------------------------------------------------------------------------------------------------------------- create procedure sp_matching @pid Int ASdeclare @tbl table (MAID int, image varchar(100)) insert @tblselect MAID, image from productswhere pid in (select top 10 mid from matching1 where pid=@pid order by newid()) select * from @tbl GO --------------------------------------------------------------------------------------------------------------------------------- When I run this sp, i have table with 10 rows. But what I really need to do is to store all data in one row like: PID, MAID1, Image1, MAID2, Image2, ..... MAID9, Image9, MAID10, Image10 How can I do this? Please help me out for this. Thanks.
View Replies !
1 Row Retrned For 3 Rows Of Data
Here are my rowsTimeIn TimeOut Reason------------------------------------01/01/04 8:00 01/01/04 12:00 Out - Lunch01/01/04 12:00 01/01/04 1:00 Lunch Hour01/01/04 1:00 01/01/04 5:00 Out - FTDHere is what I want to return from this dataDate In Out/Lunch In/Lunch Out / For the day---------------------------------------------------------01/01/04 8:00 12:00 1:00 5:00How can I do this? I have thought Curser, or Temp table, but am unsure.Appreciate any helpthanks
View Replies !
Ordering Of Data Rows
It is one of the covenants of DBMS that data rows are retrieved in no particular order, and I have learned not to rely on that. However, I'd like to know under what circumstances could rows be returned, e.g., as a result of a simple query on SQL Server, not in the order they were stored. I have never seen this happen so far. I can't even test my sorting algorithms (on the data after it gets retrieved from the server) since it always comes already sorted. Naturally, I can always simulate this scenario (and I have) but this is kind of a "conflict of interest", I'd like to see "the real thing". Any ideas? Kamen
View Replies !
Missing Data/rows
Hello, I have a sql 2000 database in which reports are generated on a monthly basis from the data inside on of my tables. The reports have been working fine, until some of the rows seemed to have disappered! I know the data use to be in the table, since it is showing on the old reports, however, when I try to pull that same data, it is not in the database at all. Does anyone have any ideas on what could have caused this or how I can resolve?? Thxs!!
View Replies !
Data Rows To Skip Does Nothing
I have put an expression on the data rows to skip in a flat file connection, however it is having no affect. I have tried setting delay validation but still nothing. Any tips
View Replies !
How To Group Certain Rows And Get The Data.
Hi Folks, I am stuck in forming a query. My age wise employee count sample data (department wise) is as shown below. Sample Data ----------- Department [>55] [50-55] [<50] [<40] [<30] ---------- ----- ------- ----- ----- ----- Marketing 0 1 5 10 20 Op's Support 0 3 6 5 25 Op's Tech 0 0 0 3 10 Product Tech 0 0 2 4 12 Product Support 0 0 1 3 7 I would require the data (sum of employee count age wise) to be categorized at a boarder level. Each category comprising of one or more departments. Operations [Op's Support + Op's Tech], Product [Product Tech + Product Support], Others [Marketing] The expected result would be. Category [>55] [50-55] [<50] [<40] [<30] [Total] --------- ----- ------- ----- ----- ----- ------- Operations 0 3 6 8 35 52 Product 0 0 3 7 19 29 Others 0 1 5 10 20 36 Thanking you in anticipation. Jabez.
View Replies !
CRACK DATA IN ROWS
THIS FUNCTION WILL HELP YOU CRACK DATA OF SINGLE COLUMN OR STRING INTO ROWS CREATE FUNCTION [dbo].[CrackInRows] (@delim varchar(1), @Phrase2Crack as varchar(8000)) RETURNS @CrackRow table ( INROWS varchar(1000) ) as BEGIN insert @CrackRow Select NullIf(SubString(@Delim + @Phrase2Crack + @Delim , IDNos , CharIndex(@Delim , @Delim + @Phrase2Crack + @Delim , IDNos) - IDNos) , '') AS INROW FROM IDNos WHERE IDNos <= Len(@Delim + @Phrase2Crack + @Delim) AND SubString(@Delim + @Phrase2Crack + @Delim , IDNos - 1, 1) = @Delim AND CharIndex(@Delim , @Delim + @Phrase2Crack + @Delim , IDNos) - IDNos > 0 return END Kapil Arya
View Replies !
Asp.net 2, Sql2005, Double Data Rows
I'm stumped. When using the classic asp page, the table produced on the web has 4 rows. But, when using the asp.net 2.0 side against the same stored proc, I get double the rows - 8. However, when I pull from the sql table directly there are only 4 rows. Any ideas? I've looked at this far too many hours...da = New Data.SqlClient.SqlDataAdapter("pTblTempDisplayInsert2 0,'" & coList & "','" & tblList & "','" & varTblNameT & "'", cn) da.Fill(ds, "myTables") For Each drow As System.Data.DataRow In ds.Tables("myTables").Rows varTxt &= drow.Item( "titleID") & "<br />" Next I get:18521850205520531852185020552053If I run the last line of the stored procedure with the variable table name filled in:SELECT d.titleID FROM ##varTblName d LEFT JOIN xtblTitles t ON d.tblID=t.tblID LEFT JOIN tblStatTitles s ON d.titleID=s.titleID LEFT JOIN xtblURL u ON u.urlID=t.urlID ORDER BY d.tblID, d.[year], d.lineOrder, d.countyID I get:1852185020552053If I do a simple select * from ##varTableName, I get four rows....Thanks,Janet
View Replies !
Unique Rows Of Data Query
How would I get the unique email addresses and its associated row of data from a SQL Server table that has no unique fields defined? If there is a duplicate email address then only show the first one and not the other rows with the same email address. Example table and data UserID LastName Email997249 MCCO-49 S.MCCO-49@SampleISD.org997462 BATE-62 A.BATE-62@SampleISD.org997605 DENS-05 B.DENS-05@SampleISD.org 997622 KAIS-22 A.KAIS-22@SampleISD.org997623 KAIS-22 A.KAIS-22@SampleISD.org997624 KAIS-22 A.KAIS-22@SampleISD.org997625 KAIS-22 A.ZKAIS-22@SampleISD.org997626 KAIS-22 AX.ZKAIS-22@SampleISD.org997627 KAIS-22 AX.KAIS-22@SampleISD.org Result UserID LastName Email997249 MCCO-49 S.MCCO-49@SampleISD.org997462 BATE-62 A.BATE-62@SampleISD.org997605 DENS-05 B.DENS-05@SampleISD.org 997622 KAIS-22 A.KAIS-22@SampleISD.org997625 KAIS-22 A.ZKAIS-22@SampleISD.org997626 KAIS-22 AX.KAIS-22@SampleISD.org Thanks
View Replies !
|
TRACKER
