Getting Multiple Rows In A Single Row
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 Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Single Row Into Multiple Rows
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 Replies !
View Related
Spliting Single Row Into Multiple Rows
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 Replies !
View Related
Multiple Rows Into A Single Field
Hi I have aproble with stored procedure.I want to take the Data from a table with multiple rows,In the same select statement for the others select statemet.My store Proc is like this.. CREATE procedure spr_Load_TR_AccidentReport_Edit_VOwner ( @Crime_No varchar(20), @Unit_ID int ) as begin DECLARE @AD_Driver int,@AC_Cas int,@AV_Owner int,@A_Witness int DECLARE @Defect_ID varchar(100) select @AV_Owner=Vehicle_Owner from TBL_TR_ACCIDENT_VEHICLE where Crime_No =@Crime_No and Unit_ID = @Unit_ID SELECT TBL_TR_Person_Details.Person_ID,TBL_TR_Person_Details.Person_Name, dbo.TBL_TR_Person_Details.Address1, dbo.TBL_TR_Person_Details.Address2, dbo.TBL_TR_Person_Details.City_Id, dbo.TBL_TR_Person_Details.State_Id, dbo.TBL_TR_Person_Details.Nationality_id, dbo.TBL_TR_Person_Details.EMail, dbo.TBL_TR_Person_Details.Phone, dbo.TBL_TR_Person_Details.zip, dbo.TBL_TR_Person_Details.sex, dbo.TBL_TR_Person_Details.D_O_B, dbo.TBL_TR_Person_Details.Age, dbo.TBL_TR_Person_Details.Occupation_ID, dbo.TBL_TR_Person_Details.Person_Type, TBL_TR_ACCIDENT_VEHICLE.Registration_Number, TBL_TR_ACCIDENT_VEHICLE.Crime_No, TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner, TBL_TR_ACCIDENT_VEHICLE.Vehicle_Type, TBL_TR_ACCIDENT_VEHICLE.Vehicle_Vanoeuvre, TBL_TR_ACCIDENT_VEHICLE.vehicle_Make, TBL_TR_ACCIDENT_VEHICLE.Vehicle_Model, TBL_TR_ACCIDENT_VEHICLE.Unit_ID, TBL_TR_ACCIDENT_VEHICLE.RowID, TBL_TR_ACCIDENT_VEHICLE.UserID, TBL_TR_ACCIDENT_VEHICLE.Vehicle_Color, TBL_TR_ACCIDENT_VEHICLE.HP, TBL_TR_ACCIDENT_VEHICLE.Seating_Capacity, TBL_TR_ACCIDENT_VEHICLE.Class_Of_Vehicle, TBL_TR_ACCIDENT_VEHICLE.Unladen_Weight, TBL_TR_ACCIDENT_VEHICLE.Registered_Laden_Weight, TBL_TR_ACCIDENT_VEHICLE.Skid_Length, (select TBL_TR_Person_OutsideDetails.OutSide_state from TBL_TR_Person_OutsideDetails,TBL_TR_ACCIDENT_VEHICLE where TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner = TBL_TR_Person_OutsideDetails.Person_id and TBL_TR_ACCIDENT_VEHICLE.RowID =TBL_TR_Person_OutsideDetails.RowID)[OutSide_state], (select TBL_TR_Person_OutsideDetails.OutSide_City from TBL_TR_Person_OutsideDetails,TBL_TR_ACCIDENT_VEHICLE where TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner = TBL_TR_Person_OutsideDetails.Person_id and TBL_TR_ACCIDENT_VEHICLE.RowID =TBL_TR_Person_OutsideDetails.RowID)[OutSide_City] ---here I faced the problem- /*For the above Select only return one rows.But this select willreturn multiple row .I wnat to put that multiple data into a single field with comma*/ (SELECT @Defect_ID = COALESCE(@Defect_ID + ',','') + CAST(TBL_TR_VEHICLE_DEFECT.Defect_ID AS varchar(5)) FROM TBL_TR_VEHICLE_DEFECT,TBL_TR_ACCIDENT_VEHICLE WHERE TBL_TR_VEHICLE_DEFECT.Registration_Number =TBL_TR_ACCIDENT_VEHICLE.Registration_Number) select @Defect_ID FROM tbl_TR_Accident_report,TBL_TR_Person_Details,TBL_TR_ACCIDENT_VEHICLE where tbl_TR_Accident_report.Crime_No=@Crime_No and tbl_TR_Accident_report.Unit_ID=@Unit_ID AND TBL_TR_ACCIDENT_VEHICLE.Crime_No=@Crime_No AND TBL_TR_Person_Details.Person_ID = TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner end GO
View Replies !
View Related
How To Merge Multiple Rows Into Single Row
Hi, I have two tables of news feed NewsHeader & NewsDetails NewsHeader: Time Header 10:15:34 AM News1 10:15:34 AM News1 10:15:34 AM News1 11:19:39 AM News2 11:19:39 AM News2 12:35:04 PM News3 12:35:04 PM News3 NewsDetails Time Text RowC 10:15:34 AM ABC 1 10:15:34 AM DEFG 2 10:15:34 AM HIJKL 3 11:19:39 AM AABB 1 11:19:39 AM CCDD 2 12:35:04 PM ZZYY 1 12:35:04 PM XXWW 2 Required Output Time Header Text 10:15:34 AM News1 ABCDEFGHIJKL 11:19:39 AM News2 AABBCCDD 12:35:04 PM News3 ZZYYXXWW Thank you.
View Replies !
View Related
How To Display Multiple Rows Of A Table In Single Row
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 Replies !
View Related
Inserting Multiple Rows With A Single INSERT INTO
Hi,I have an application running on a wireless device and being wireless Iwant it to use bandwidth as efficiently as possible. Therefore, I wantthe SQL statement that it uploads to the SQL Server to be as efficientas possible. In one instance, I give it four records to upload, whichcurrently I have as four seperate SQL statements seperated by a ";".However, all the INSERT INTO... information is the same each time, theonly that changes is the VALUES portion of each command. Also, I haveto have the name of each column to receive the data (believe it or not,these columns are only a small subset of the columns in the table).Here is my current SQL statement:INSERT INTO tblInvTransLog ( intType, strScreen, strMachine, strUser,dteDate, intSteelRecID, intReleaseReceiptID, strReleaseNo, intQty,dblDiameter, strGrade, HeatID, strHeatNum, strHeatCode, lngfkCompanyID)VALUES (1, 'Raw Material Receiving', '[MachineNo]', '[CurrentUser]','3/21/2005', 888, 779, '2', 5, 0.016, '1018', 18, '610T142', 'K8',520);INSERT INTO tblInvTransLog ( intType, strScreen, strMachine, strUser,dteDate, intSteelRecID, intReleaseReceiptID, strReleaseNo, intQty,dblDiameter, strGrade, HeatID, strHeatNum, strHeatCode, lngfkCompanyID)VALUES (1, 'Raw Material Receiving', '[MachineNo]', '[CurrentUser]','3/21/2005', 888, 779, '2', 9, 0.016, '1018', 30, '14841', 'B9', 344);Since the SQL statement INSERT INTO portion remains the same everytime, it would be good if I could have the INSERT INTO portion onlyonce and then any number of VALUES sections, something like this:INSERT INTO tblInvTransLog (intType, strScreen, strMachine, strUser,dteDate, intSteelRecID, intReleaseReceiptID, strReleaseNo, intQty,dblDiameter, strGrade, HeatID, strHeatNum, strHeatCode, lngfkCompanyID)VALUES (1, 'Raw Material Receiving', '[MachineNo]','[CurrentUser]', '3/21/2005', 888, 779, '2', 5, 0.016, '1018', 18,'610T142', 'K8', 520)VALUES (1, 'Raw Material Receiving', '[MachineNo]','[CurrentUser]', '3/21/2005', 888, 779, '2', 9, 0.016, '1018', 30,'14841', 'B9', 344);But this is not a valid SQL statement. But perhaps someone with a morecomprehensive knowledge of SQL knows of way. Maybe there is a way tostore a string at the header of the command then use the string name ineach seperate command(??)
View Replies !
View Related
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 !
View Related
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 !
View Related
Return Multiple Rows Into One Single String
Hi, I need to return multiple rows into one single string Declare @String varchar(1000) Create table Cus (CusId Int,CusName varchar(10)) Insert into Cus Select 1,'John' Union All Select 2,'Bob' Select * from Cus returns 1John 2 Bob I need to return the all the rows from Cus table into a single string. The return is dynamic. I do not know the number of rows returned My result should be @String = 1,John,2,Bob How can i do that ?
View Replies !
View Related
Update Multiple Rows In A Single Query?
I know this isn't right but I'm trying to build a single query in PHP to re write the sortorder column starting at 0 and writing every row in order. Code: update categories set (sortorder=0 where catid=32), (sortorder=1 where catid=33),(sortorder=2 where catid=36) where userid=111 PHP Code: $qt="update categories set "; for($i=0;$i<$num;$i++){ $a=$i+1; $qt.="sortorder=$i"; if($a<$num){ $qt.=", "; } } $qt.=" where userid=111"; Using PHP I can amend the loop above to slot in a row I want so I can change the sort order. unfortunately I'm not sure how to build such a query in mssql, can anyone help?
View Replies !
View Related
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 !
View Related
SQL 2000: Inserting Multiple Rows Into A Single Table
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 Replies !
View Related
Converting Repeating Fields In Single Row To Multiple Rows
Hi there I have loaded a csv file into a table. Some fields within the file contain a varying number (up to 10) of subfields seperated by line feed characters. It looks sort of like this Customer No. Payments Dates 111 pay1|pay2|pay3 dat1|dat2|dat3 I created an Unpivot transformation, and I got Customer No. Description Detail 111 Payments pay1|pay2|pay3 111 Dates dat1|dat2|dat3 After a Derived Column transformation I got Customer No. Description Line1 Line2 Line3 111 Payments pay1 pay2 pay3 111 Dates dat1 dat2 dat3 But what I really want is to end up with this: Customer No. Payments Dates 111 pay1 dat1 111 pay2 dat2 111 pay3 dat3 Is there a transformation that will get me there, or do I just need some cunning SQL? I tried to Pivot my way back to happiness but I couldn't get it to work
View Replies !
View Related
Copying Rows From Multiple Tables To A Single Table
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 Replies !
View Related
Concatenate Column Value From Multiple Rows Into A Single Column
Hello, I need to concatenate a column from multiple rows into a single column in a new table. How can I do this? SID NAME PGROUP ------------------------------------------------------------ 3467602 CLOTHINGACTIVE 3467602 CLOTHINGDANCE 3467602 CLOTHINGLWR Need to have SID NAME PGROUP ------------------------------------------------------------ 34676 02 CLOTHING ACTIVE , DANCE, LWR THANK YOU
View Replies !
View Related
Multiple Columns With Different Values OR Single Column With Multiple Criteria?
Hi, I have multiple columns in a Single Table and i want to search values in different columns. My table structure is col1 (identity PK) col2 (varchar(max)) col3 (varchar(max)) I have created a single FULLTEXT on col2 & col3. suppose i want to search col2='engine' and col3='toyota' i write query as SELECT TBL.col2,TBL.col3 FROM TBL INNER JOIN CONTAINSTABLE(TBL,col2,'engine') TBL1 ON TBL.col1=TBL1.[key] INNER JOIN CONTAINSTABLE(TBL,col3,'toyota') TBL2 ON TBL.col1=TBL2.[key] Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record. I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information. for e.g.; i was thinking to concatinate both fields like col4= ABengineBA + ABBToyotaBBA and in search i use SELECT TBL.col4 FROM TBL INNER JOIN CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1 ON TBL.col1=TBL1.[key] Result = 1 row But it don't work in following scenario col4= ABengineBA + ABBCorola ToyotaBBA SELECT TBL.col4 FROM TBL INNER JOIN CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1 ON TBL.col1=TBL1.[key] Result=0 Row Any idea how i can write second query to get result?
View Replies !
View Related
Multiple Databases And Multiple Exe For A Single Solution
Dear Reader,Currently Am working on a Management Information System.Need to develop some part of the solution as almost Hard Coded Details: Both Front end and Database carry default valuse...which will never change in the near future. And some parts are depending on Changing rules ....so to be developed as separate exes...and separate databse are requires so ...Changes if needed can be adopted easily...Please guide ...How to manage abobe requirement?Please feel free to write for further clarifications.SuryaPrakash Paaatel--Message posted via http://www.sqlmonster.com
View Replies !
View Related
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 !
View Related
Obtaining Data To Be Displayed In Multiple Columns From Multiple Rows
Hello All, I am rather new to reporting on SQL Server 2005 so please be patient with me. I need to create a report that will generate system information for a server, the issue im having is that the table I am having to gather the information from seems to only allow me to pull off data from only one row. For example,. Each row contains a different system part (I.e. RAM) this would be represented by an identifier (1), but I to list each system part as a column in a report The table (System Info) looks like:- ID | System part | 1 | RAM 2 | Disk Drive 10| CPU 11| CD ROM | Which So basically I need it to look like this. Name | IP | RAM | Disk Drive| ---------------------------------------------- A | 127.0.0.1 | 512MB | Floppy So Far my SQL code looks like this for 1 item SELECT SYSTEM PART FROM System Info WHERE System.ID = 1 How would I go about displaying the other system parts as columns with info Any help is much appreciated!
View Replies !
View Related
Processing Multiple Rows And Multiple Columns In SSIS
I previously posted a problem with result set bindings but I have not been able to resolve my problem. I guess all this comes with being new to programming in this environment! Anyway, I am trying to figure out how to process from an ADO.NET connection multiple rows with multiple columns. I have to read and manipulate each row. I was originally looking at using a foreach loop but have not been able to get it to work. One reply to my previous thought I should be using a data task to accomplish this. Could someone tell me the best way to handle this situation? As a note, I am new to programming in SSIS and basically trying to learn it as I go so please bear with me! Thanks in advance!
View Replies !
View Related
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 !
View Related
SQLCE V3.5: Single SDF With Multiple Tables Or Multiple SDFs With Fewer Tables
Hi! I have a general SQL CE v3.5 design question related to table/file layout. I have an system that has multiple tables that fall into categories of data access. The 3 categories of data access are: 1 is for configuration-related data. There is one application that will read/write to the data, and a second application that will read the data on startup. 1 is for high-performance temporal storage of data. The data objects are all the same type, but they are our own custom object and not just simple types. 1 is for logging where the data will be permanent - unless the configured size/recycling settings cause a resize or cleanup. There will be one application writing alot [potentially] of data depending on log settings, and another application searching/reading sections of data. When working with data and designing the layout, I like to approach things from a data-centric mindset, because this seems to result in a better performing system. That said, I am thinking about using 3 individual SDF files for the above data access scenarios - as opposed to a single SDF with multiple tables. I'm thinking this would provide better performance in SQL CE because the query engine will not have alot of different types of queries going against the same database file. For instance, the temporal storage is basically reading/writing/deleting various amounts of data. And, this is different from the logging, where the log can grow pretty large - definitely bigger than the default 128 MB. So, it seems logical to manage them separately. I would greatly appreciate any suggestions from the SQL CE experts with regard to my approach. If there are any tips/tricks with respect to different data access scenarios - taking into account performance, type of data access, etc. - I would love to take a look at that. Thanks in advance for any help/suggestions, Bob
View Replies !
View Related
Convert Multiple Rows Into Multiple Columns
I need to push multiple rows of data from one table to one record in another table. This can be achieved using cursors but I need to do them without using cursors. Source Table: fld_name fld_value fld1 val1 fld2 val2 fld3 val3 fld4 val4 Destination should be like this: fld1 fld2 fld3 fld4 val1 val2 val3 val4 Thanks.
View Replies !
View Related
Deleting Duplicate Rows Within A Single Table
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 Replies !
View Related
Best Practice For Updating Single Field In N Rows
Hi I haven't included DDL etc as this is theoretical at this time. I can rustle up an illustrative example if required.Following applies to disconnected environment. In general, most edits are broad (i.e. n fields affected) against a single record. However, there are certain circumstances where a single field will be edited against a deep (n) set of records. So business request:User needs to access and edit n records at a time (for arguments sake n is unlikely to be enourmous - say 50 max) but only editing one single field, and always the same field. The values for this edited field will differ for each record. Initially retrieving data for the app is no problem, nor is identifying those records that have been edited. What is the best means of updating the table? There are, to my mind, three ways of dealing with the latter senario - 1) Client calls the server n times editing a record at the time.2) Client creates a csv string and passes to sproc. Sproc parses string using some UDF split function (probably chucking into table variable) and updates using a single set set based operation.3) Client creates some flat file or other that is BULK inserted (or similar) by SQL Server. 3 - I think would only be an option if n was a very large number and or having the changes immediately reflected in the data is not priority.1 - would create a chatty app and presumably put the most load on the server. So I am left thinking 2 is (depending on circumstances) the best method. Is this fair? Are there any particular considerations gotchas I should be aware of? I know where to get hold of as many TSQL split functions as I could want so I'm not looking for code just opinions. OR - am I just plain wrong? Is there a better alternative or am I dismissing the other two methods prematurely? Thanks in advance
View Replies !
View Related
Adding A Single Change To All Rows In A Column
We just changed over our phone system and the new system uses all of the old extensions except it adds a 1 to the beginning of them. I know that there is a relatively simple way to update my phone extension column to show this, but I can't for the life of me remember what I need to do. Any help?
View Replies !
View Related
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 !
View Related
Get Total Count And Top N Rows In Single Call
Hi, I want to return the total number of rows and top n records from my query. Is it possible to do this by simply running a single query. My query is dynamic so I can not create any temp table or table variables. Bacially I want to eliminate 2 calls for sp_executesql Set @ColumnCount = "Count(*)" Set @Columns = " Top 100 a,b,c" Set @Filter = "a = 1001" Set @sql = "select " + @ColumnCount + " FROM TABLEA WHERE " + @Filter sp_executesql @Sql Set @sql = "select " + @Columns + " FROM TABLEA WHERE " + @Filter sp_executesql @Sql Thanks Pun
View Replies !
View Related
Single Or Multiple Sp ???
greetings,i was wondering is it better to have multiple small stored procedures or one large store procedure ???? example : 100 parameters that needs to be inserted or delete ..into/from a table.............is it better to break it up into multiple small store proc or have 1 large store proc....thanks...............
View Replies !
View Related
Bcp Single CPU Vs Multiple CPU
I'm doing a BCP of a large table 37 million rows. On a single CPU server, SQL 7, sp 3, with 512 meg of RAM, this job runs in about 3 hours. On a 8 way server with 4 Gig of RAM, SQL 7 Enterprise, this job runs 12 hours and is only a third done. The single CPU machine is running one RAID 5 set while the 8 way server is running 4 RAID 5 sets with the database spread out over two of them. Is there something obvious that a single CPU box would run this much faster?
View Replies !
View Related
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 !
View Related
Select Single Entry Found In All Rows (access2k)
I have to write a statement that answers the question: "Which pilot is authorized to fly all the aircraft in the fleet?" implying that the individual aircraft could be of any of the three aircraft types. Below are the tables with notation Tbl_name (important keys [clarification of keys]): personnel (pers_id, name, crew_role [eg. pilot, stewardess]), aircraft (craft_id, type_designation [eg. Boeing737], craft_name [eg. The Viking, Icarus]), aircraft_type (type_designation), and authorization (pers_id, type_designation). In words my question should be something like: "For all those who are authorized to fly aircraft (the only pers_id:s listed in the authorization table), show the names of those that have their pers_id:s next to all aircraft_type:s." I've gotten this far: Code: SELECT DISTINCT p.name FROM personnel AS p, authorization AS b, aircraft_type AS f WHERE p.pers_id = b.pers_id AND b.type_designation = ... ; Now what I want to accomplish is to select the pilot which has a record for ALL type_designation entries in the aircraft_type table. Is there a magic keyword that I don't know of? Is that magic keyword called "EXIST" and how do I use it? Best regards, rod
View Replies !
View Related
Getting The Count Of Rows For 3 Tables In Single Execute SQL Task
hi frnds, im very new to SSIS package .my package consists of Single Exceute task. In Single Exceute SQL task i had 3 seperate queries to get the count of rows of 3 tables. the Query goes like dis ... select count(*) AS precheckcount1 from new_main_dts where cust_nbr like '875%' like dis for another 2 tables i had written with the alias name precheckcount2 and 3 i mapped the variable user::precheckcount1 to precheckcount1 in the resultSet and for other two alias name i did the same. while executing the package the error is thrown : [Execute SQL Task] Error: An error occurred while assigning a value to variable "precheckcount2": "Unable to find column precheckcount2 in the result set.". please help me its very urgent
View Replies !
View Related
|
TRACKER
