Combine Data In Single Row From Single Table
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 Complete Forum Thread with Replies
Related Forum Messages:
Combine Data In Single Row
SELECT * FROM dbo.empBenefits q WHERE (StartDate IN (SELECT TOP 2 STARTDATE FROM EMPBENEFITS WHERE EMPBENEFITS.employeeno = q.employeeno AND Benefitcode = 'HON' ORDER BY startdate ASC)) I have this select statement working however I need to combine 2 records in a single row in a single table. The unique key is Employee No.
View Replies !
Combine Multiple Records Into Single Row
This is how the data is organized:vID Answer12 Satisfied12 Marketing12 Yes15 Dissatisfied15 Technology15 No32 Strongly Dissatisfied32 Marketing32 YesWhat I need to do is pull a recordset which each vID is a single rowand each of the answers is a different field in the row so it lookssomething like thisvID Answer1 Answer2 Answer312 Saitsfied Marketing Yesetc...I can't quite get my mind wrapped around this one.
View Replies !
Can We Combine These 3 Statements Into One Single Query
SELECT 1 as id,COUNT(name) as count1 INTO #temp1 FROM emp SELECT 1 as id,COUNT(name) as count2 INTO #temp2 FROM emp WHERE name <>' ' AND name IS NOT NULL OR name <> NULL SELECT (cast(b.count2 as float)/cast(a.count1 as float))*100 AS per_non_null_names FROM #temp1 a INNER JOIN #temp2 ON a.id=b.id
View Replies !
Script To Combine Multiple Rows Into 1 Single Row
Hi,I'm working on a system migration and I need to combine data from multiplerows (with the same ID) into one comma separated string. This is how thedata is at the moment:Company_ID Material0x00C00000000053B86 Lead0x00C00000000053B86 Sulphur0x00C00000000053B86 ConcreteI need it in the following format:Company_ID Material0x00C00000000053B86 Lead, Sulphur, ConcreteThere is no definite number of materials per Company.I have read the part ofhttp://www.sommarskog.se/arrays-in-sql.html#iterative that talks about 'TheIterative Method' but my knowledge of SQL is very limited and I don't knowhow to use this code to get what I need.Can anyone help me?
View Replies !
Script To Combine Multiple Rows Into A Single Row
Hi everyone,I really appreciate if anyone could help me with this tricky problemthat I'm having. I'm looking for a sample script to combine data inmultiple rows into one row. I'm using sqlserver. This is how data isstored in the table.ID Color111 Blue111 Yellow111 Pink111 GreenThis is the result that I would like to have.ID Color111 Blue, Yellow, Pink, GreenThere is no definite number of colors per ID. I have to use ID togroup these colors into one row. Therefore, ID becomes a unique keyin the table.Appreciate your help and time. Thank you in advance
View Replies !
Combine Multiple Rows Into Single SQL Record
Hello: I have the following table. There are eight section IDs in all. I want to return a single row for each product with the various section results that I have information on. productID SectionID statusID 10 1 0 10 2 1 10 3 2 10 4 1 10 5 3 10 6 1 11 1 0 11 2 1 11 3 2 11 7 3 11 8 3 Need to return two rows with the respective values for each section. productID section1 section2 section3 section4 section5 section6 section7 section8 10 0 1 2 1 3 1 11 0 1 2 3 3 Any information or if you can point me in the right direction would be appreciated. Thanks
View Replies !
How Can I Combine Values Of Multiple Columns Into A Single Column?
Suppose that I have a table with following values Table1 Col1 Col2 Col3 ----------------------------------------------------------- P3456 C935876 T675 P5555 C678909 T8888 And the outcome that I want is: CombinedValues(ColumnName) ---------------------------------------------- P3456 - C935876 - T675 P5555 - C678909 - T8888 where CombinedValues column contains values of coulmn 1,2 & 3 seperated by '-' So is there any way to achieve this?
View Replies !
Retrieving Hierarchical Data From A Single Table
I would like to retrieve a hierarchical list of Product Categories from a single table where the primary key is a ProductCategoryId (int) and there is an index on a ParentProductCategoryId (int) field. In other words, I have a self-referencing table. Categories at the top level of the hierarchy have a ParentProductCategoryId of zero (0). I would like to display the list in a TreeView or similar hierarchical data display control.Is there a way to retrieve the rows in hierarchical order, sorted by CategoryName within level? I would like to do so from a stored procedure. Example data:ProductCategoryID CategoryDescription ParentProductcategoryID ParentCategoryDescription Level------------------------------------------------------------------------------------------------------------------------------------------------1 Custom Furniture 0 02 Boxes 0 03 Toys 0 04 Bedroom 1 Custom Furniture 15 Dining 1 Custom Furniture 16 Accessories 1 Custom Furniture 17 Picture Frames 6 Accessories 28 Serving Trays 6 Accessories 29 Entertainment 1 Custom Furniture 110 Planes 3 Toys 111 Trains 3 Toys 112 Boats 3 Toys 113 Automobiles 3 Toys 114 Jewelry 2 Boxes 115 Keepsake 2 Boxes 116 Specialty 2 Boxes 1Desired output:Custom Furniture Accessories Picture Frames Serving Trays Bedroom Dining EntertainmentBoxes Jewelry Keepsake SpecialtyToys Automobiles Boats Planes Trains
View Replies !
Help With Creating SQL Statement To Get Data From Single Table...
Hi, I'm having some difficulty creating the SQL Statement for getting some data from a table: I have the following table of data __User___Votes___Month __A_______14______2__A_______12______3__A_______17______4__A_______11______5 __B_______19______2__B_______12______3__B_______15______4 I want to beable to pull out the total number of votes a user has had over a period of months. eg Total up each users users votes for months 4 and 5 that would give: __User____TotalVotes ___A________28___B________15 An added complecation is that user B does not have any data for month 5 Any help or pointers would be fanstatic Many thanks
View Replies !
Displaying Data In Hierarchy From Single Table..
Hi, I like to get data from a signle table and arranged in hierarchical(hierarchy) order. What will be my sql script to be able to get the desired result shown below? Please include some explanation as too what script is doing.. Table Structure and Sample Data Id ParentId Name Code DisplayOrder 1 null Group 1 G00001 1 2 null Group 2 G00002 2 3 1 Sub-Group 1 SG0001 1 4 2 Sub-Group 2 SG0002 1 5 3 Sub-Sub-Group 1 SSG001 1 6 null Group 3 G00003 3 7 3 Sub-Sub-Group 2 SSG002 2 Desired Result Id ParentId Level Name ExtendedName DisplayOrder 1 null 1 Group 1 Group 1 1 3 1 2 Sub-Group 1 Group 1 -> Sub-Group 1 1 5 3 3 Sub-Sub-Group 1 Group 1 -> Sub-Group 1 -> Sub-Sub-Group 1 1 7 3 3 Sub-Sub-Group 2 Group 1 -> Sub-Group 1 -> Sub-Sub-Group 2 2 4 2 2 Sub-Group 2 Group 1 -> Sub-Group 2 1 2 null 1 Group 2 Group 2 2 6 null 1 Group 3 Group 3 3
View Replies !
Comparing Data In Two Consecutive Rows From A Single Table
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 Replies !
Loading The Different Language Data From Excel File To The Single Table
can anyone help me to solve this problem i have created a ssis package to load the data from excel file to the table, but we are getting the data in different language ie in french,english and in china after loading the data when we view the data it is showing as junk characters for chinese data but we are able to see other language data ie french and english. so please tell me how to solve that reply to my mail id(sandeep_shetty@mindtree.com)
View Replies !
Data Flow Task - Multiple Columns From Different Sources To A Single Table
Hi: I have a data flow task in which there is a OLEDB source, derived column item, and a oledb destination. My source is a SQL command, that returns some values. I have some values, that I define in the derived columns, and set default values under the expression column. My question is, I also have some destination columns which in my OLEDB destination need another SQL command. How would I do that? Can I attach two or more OLEDB sources to one destination? How would I accomplish that? Thanks MA2005
View Replies !
BCP A Single Table
I've managed to BCP in a single table form a backup DAT file into a database, but it took WAY to long (1 hour+ on a meaty server) and I can't understand why. The table only had a few rows of data and only had a few small dependancy tables. The table has a primary key, hence an index so the BCP becomes a logged operation but it still should not take this long. Here is the BCP command line that I ran :- bcp <dbname>.dbo.<tablename> in <DAT filename> /U sa /P /S <server> /m 1 /n Could anyone please shed some light on this. Is there anyway of 'fast' bcp'ing this table into the database overwriting the existing one. Failing that, is there any way of scripting the transfer of a table from one server to another. Many thanks. Dave
View Replies !
Deadlock On Single Table
We have one user who enters a transaction and then does a single rowupdate (updates all columns but only one is changing - this is due tothe way our sql is generated in the application), at this pointanother user enter a transaction and tries to update the same row (heunderstandably has to sit and wait while he is blocked by the originaluser). The original user then updates the same row again – at thispoint the second user is chosen as a deadlock victim and killed. If Itry and recreate this with any other tables(or pubs) I get my expectedbehaviour of the original user just doing 2 successful updates and thesecond user then completing his update once the original user haseither committed his changes or rolled back. The query plan indicatesthat a drop and insert of the row is happening (this is not the casewith any other tables where we get our expected behaviour). This onlyhappens when the index is clustered - if we use a non-clustered indexit does not occur.Is this expected behaviour? it seems dangerous to me as the firstuser has not commited or rolled back his updates. It was onlyhighlighted by a fault in our application that caused the secondupdate to be executed.I have some thoughts about it being something to do with a row lockbeing relased due to a delete / insest of the row in the second update(we see this in the execution plan).....Any help much appreciated as I am struggling to get my head round howthe second user was ever able to get hold of the resource.
View Replies !
Intersection On A Single Table
Hi all ! I have a table with no keys (temp table) which looks like this : col1|col2|col3 001|A|.087 001|B|.032 001|C|.345 002|A|.324 002|B|.724 003|A|.088 003|C|.899 001|A|.087 001|A|.234 001|B|.032 As you see, there is some duplicate entries in it. I would like to get a list of all the rows that have the same col1 and col2 BUT different col3 value. The result should return col1=001 col2=A but NOT col1=001 col2=B. I tried a lot of queries with EXISTS, HAVING, etc... but nothing seems to work. Anyone have an idea how I can do it ?
View Replies !
Single Table Restore
Hi! This morning, I needed to restore 1 table to a database because it had accidentally been deleted (don't ask). Each time I tried to restore the single table, I would select TOOLS| DATABASE BACKUP/RESTORE| RESTORE tab from Enterprise Manager, then I select SINGLE TABLE, but am only given the existing table names as choices to restore. This happens when I select to restore 'FROM DEVICE' as well. Am I doing something wrong or is this how MS SQL6.5 is supposed to work? Am I only able to restore a corrupt table and not a missing table? I ended up restoring the entire database... Thanks for your help! Toni Eibner
View Replies !
Single Table Load
I thought I had posted this question already, but didn't see it in the list. I apologize if this is a repost. I am running SQL Server 6.5 SP 4. I am attempting to load a single table from backup, but continually get the error about schemas not matching. Interestingly, it comes back with a status 4, and not the status 3 indicating a mismatch on Ansi_Padding. The statement I am using is load table demhist from internal_tape with file=5,nounload I have tried creating the table from scratch and using select * into... I have tried both above with both settings of Ansi Padding. The table I am trying to load contaings char columns which allow nulls. I have experimented with loading 2 other tables. One of the other tables loads and one doesn't. The one which does not load also has char columns which allow nulls, while the one that does load does not have char columns which allow nulls. Is it a known problem or limitation on the table load that it cannot reload tables that contain char columns which allow nulls?
View Replies !
Single Table Restoration
It appears that in SQL 2000 restoring a single table is a bit more tedious and less user friendly as in earlier versions (6.5). Can anyone explain in a nutshell the easiest/quickest way to restore a single table from a backup? Thanks, Christine
View Replies !
Backup A Single Table
I have a table with about 5000000 Ml records that I need to copy to other database. What will be the easy way to do it? Can I backup a single table? Any suggestions!
View Replies !
Restore Single Table
How can I restore just one table from the full backup? It was very easy with 6.5 but I am not sure whether it's possible with 7.0 and if it is possible, how to do it. Thanks in advance.
View Replies !
Restore A Single Table
Does anyone know of a way to restore a single table using SQL 7.0? I know that I can build a dummy database and do a database restore, then copy the table from database to database. But....was wondering if there is a way to restore a single table from a backup. Is losing the ability to restore a single table one of the 'features' of 7.0?
View Replies !
Two Different Datasets In A Single Table.
Hi, I need to display the datas in a table format. I have placed a table and now i need to use two different datasets for a single table. Those two different datasets are generated based on two different stored procedures. Those stored procedures retrieve data from two different sql tables. Can any one explain me how to use two different datasets for a single table. As far i have tried i was able to fetch only the first record of the other dataset. But i need to fetch all the records of both the dataset. Thanks in advance for any help. Thanks, Sangeethaa
View Replies !
Restore A Single Table
Hi, I have a problem. For some reason I needed to restore a single (large) table that shares a filegroup with other tables. I have a full backup of the database. How can I restore my particular table? -- Srinivas
View Replies !
How To Check If A Single Value Exists In A Table
What’s the easiest way to check if a single value exists in a table column? I’m building a simple login page, and I want to get the username and check if it exists in my Users table. Here’s my SQL: SELECT UserID FROM UsersWHERE UserID = "admin" Could someone give me code to check for “admin” in my UserID column? Here’s some code I tried, using my SqlDataSource, but it returns an error “Could not load type 'System.Data.OleDb'” protected void Button1_Click(object sender, EventArgs e) { // Retreive the results from the SqlDataSource as a DataReader OleDbDataReader reader = (OleDbDataReader) SqlDataSource1.Select(DataSourceSelectArguments.Empty); // Read in the value if (reader.Read()) { } // Close the reader reader.Close(); } I don’t have to use the SqlDataSource, but originally thought it might be easier. I know how to use SqlDataSource to fill a whole GridView but this is different.
View Replies !
Select 1 Row From Each Group In A Single Table
Hello everyone, I've got a bit of an SQL mystery that I'm not sure how to solve. For some reason I just cant get my head around it. Here's the scenario: Table A: _____________ BidID - Int identity AuctionID - int BiderName - varchar(50) bidAmount - money ______________________ Now obviously each Bid will have a Unique ID using BidID but the other rows will contain multiple bids per user, on many different items possibly. BidID AuctionID BiderName BidAmount 1 4005 joeblow 100.00 2 4005 janedoe 101.00 3 4005 joeblow 107.00 4 4006 joeblow 100.00 5 4006 janedoe 105.00 6 4006 joeblow 106.00 I need to find out which Auctions JoeBlow is bidding on, but I dont need a table with Rows for every single one of his bids, just a distinct auctionID for his top bid so in this case the only thing returned would be 3 4005 joeblow 107.00 6 4006 joeblow 106.00 Any clues? I've been through sub querys, and stored procedures, and I cant get anything to work quite right. Thanks in advance for your help.
View Replies !
Will A Single Row Be Locked Or The Entire Table?
I am giving the following statement outside the begin tran block in a stored procedure: select * from table1 with (holdlock) where column1 = @colValue I have the following two questions regarding above situation: Being outside the begin tran block, will the select statement cause a lock to be held or it has to be within the begin tran block for the lock to be held? Will the select statement hold a lock only on the row or rows matching the where clause, or it may lock a page or even the entire table in this situation? Thanks
View Replies !
Grant Access To A Single Table
So here's the situation: I'm creating a front-end application to an SQL Server 2000 database and I need to create a new login to the database for the app. Is it possible (or even; is it a good idea...) to create a logon that only has access to a single table? Actually, I know it's possible - but I don't want to have to go through the hundreds of tables and deny access to them all! So I guess the question is; Is there a quick and easy way of granting a login permissions to a single table only?
View Replies !
SIngle Table Backup/Restore
Hi guys, How to make single table backup / or Restore individual table from the Database backup file.SQL server 6.5 got an option to do this...Anyone know in SQL 7.0 or 2000 I appreciate ur immediate reply! Thanks Rajeev
View Replies !
Single Table BACKUP/RESTORE
Gentlemen: SQL 6.5 used to support single table backup and single table restore from a full backup. I find no such feature in SQL 7.0. Is it definitely no more supported? Best regards and ciao, Andrea
View Replies !
Slow SELECT On Single Table
SELECT * on a 4000 row table is taking more than 12 seconds. Other larger tables are not nearly as slow. I've DBCC dbreindex'd, and dbcc showcontig shows density at 100%. How can I figure out why this is happening? What are some remedies? Thanks for your help.
View Replies !
Can I BCP A Single Colume Into An Exsisting Table?
I need to update one column in a very large table. Can I bcp, bulk copy, bulk insert... one column from another table into an existing table? For example: table RES column STATUS needs to be updated with the same column from another table (PROP). I have looked at bcp but it looks like it wants to insert/update all columns in the row. I just need the one column updated and I need it done fast. Any help would be appreciated. Charlie.
View Replies !
4 Lookups Against Single Large Table
I need to do a 4 column lookup against a large table (1 Million rows) that contains 4 different record types. The first lookup will match on colums A, B, C, and D. If no match is found, I try again with colums A, B, C, and '99' in column D. If no match, try again with column A, B, D, and '99' in Column C. Finally, if no match in any of the above, use column A, '99' in B, '99' in C, '99' in D. I will retreive 2 columns from the lookup table. My thought is that breaking this sequence out into 4 different tables/ lookups would be most efficient. The other option would be to write a script that handled this logic in a single transform with an in-memory table. My concern is that the size of the table would be too large to load into memory. Any ideas/suggestions would be appreciated.
View Replies !
Mutiple Table Single Task
I have about 100 different tables that I would like to bring on nightly basis via integration services. SSIS will process the data and send on to its warehouse destination. Is it possible to use a single task to bring in all these tables from within SSIS? and can write failed records to flat files at the sametime? instead of defining a data flow for each table and dealing with hundreds of dataflows just have one task that loop through the list of tables. Wonder how warehouses fed by 100s of tables deal with this kind of scenario?
View Replies !
Splitting Single Row Of Table In To Two Rows
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 Replies !
Date Difference From A Single Table
Hi, I have a table VisitLog pkey customer_id pkey user_id visit_date visit_note Given both id's, I need a report that will list the visit interval. Assuming the rows are entered in ordered. the result should just display a single column with rows filled with date difference. Date Format in example below is dd/MM/yyyy 1, 1, '01/01/07', null 1, 1, '03/01/07', null 1, 1, '08/01/07', null The result should list difference in days. 2 5 Is it possible to do this in a SELECT Statement? Thanks, Max
View Replies !
Convert Single Record To Table
Hi all! I have imported a table into SQL Server from a legacy program. Each record has a repeating sequence of similar fields. (Ex. Accnt1, Assesed1, Paid1, Accnt2, Assesed2, Paid2, etc.) I would like to take a single record and put data from these fields into a table that has the columns Accnt, Assesed, and Paid. I am doing this for easier use in a program I am developing in VB 2005. Can this be done in SQL or do I need to have help from some VB code? If it's possible, what might the SQL look like? Thanks.
View Replies !
Exporting A Single Table From A Database
Hello, Let me frist start saying that I am no SQL DB guru or have any great knowledge. I am sure this questions is the most basic question posted here. I would like to know how to export a single table from an SQL 2005 DB. I need to export this table from one SQL server to another. Just one table and its contents. Also, I woud like this exported table to be saved as a *.dat file? Thanks in advanced.
View Replies !
How To Copy Single Table From Database
Hello all, I created one Database in my server,n i wanted the same database in my local computer.So i copied it and restored in my database. Now i created another one new table in my server. I want to copy that table into my computer. How can i copy a single table in database and how can i restore(paste) that into my existing databse of my local compuer. Can u please help me to resove this problem. Thanks. rita
View Replies !
Get Child Value From A Single Table Structure
Hi I have a single table which holds a hierarchical a structure. I would like to get all the child ID's from one Stored Proc call. For example I give it an ID in the structure and I would like to give me all the child records that exist to the lowest level for each category found My table structure is [SvcID] [varchar](30) NOT NULL, [Ser_SvcID] [varchar](30) NULL, [SvcDesc] [varchar](100) NOT NULL I know where the root of the hierarchy is because Ser_SvcID will be null. Im not sure how to do this in one stored procedure call Hope the above makes sense Many thanks in advance
View Replies !
|
TRACKER
